LMHmedchem <lmh_users-gro...@molconn.com> wrote: > I have data that I am loading into a sqlite database from a text file. I am > using a composite primary key for four ints for the main table. > > create table Structure ( > i1 integer not null, > i2 integer not null, > i3 integer not null, > i4 integer not null, > name string not null, > filePath string not null, > SMILES string not null, > formula string not null, > fw float not null, > primary key (i1, i2, i3, i4) > ) > > For this table, there should be a reasonable syntax to insert and select > using the composite primary key values to find things. My understanding is > that the composite key values will be hashed, leading to a fast look up. I > am not entirely sure of the syntax, but I believe that this is a sound > structure.
No special syntax, just plain select * from Structure where i1=:value1 and i2=:value2 and i3=:value3 and i4=:value4; SQLite is smart enough to use the key for such a query. Note that "string" doesn't have any special meaning in SQLite; you are creating columns with no affinity. It's better to use "text" instead. For details, see http://sqlite.org/datatype3.html > There is more data for each record that will go into other tables. The > question I have is how to best keep the data in the different tables in > registration, meaning to make sure that I can retrieve all of the data from > the record from all tables using the same 4 primary key values. If you have a one-to-one relationship between two tables, is there a reason why you don't simply combine the two into a single, wider table? > I could create the same primary key in every table, > > create table Identifier( > i1 integer not null, > i2 integer not null, > i3 integer not null, > i4 integer not null, > CSpider string, > KEGG string, > CAS string, > primary key (i1, i2, i3, i4) > ) > > but the notion of having multiple primary keys doesn't seem quite right. Why is that? Basically, every table needs a primary key, whether composite or otherwise. If this tuple of integers is the natural key for your data, I don't see a problem. > It also seems as if there should be a way to record the rowid of where a > record went in the first table and I should be able to use that to insert > data from the same record to the same rowid of other tables. There is - see http://sqlite.org/autoinc.html . Change your table to create table Structure ( id integer primary key, i1 integer not null, i2 integer not null, i3 integer not null, i4 integer not null, ... unique (i1, i2, i3, i4) ); Now, you can insert a record while leaving 'id' column out, and it will be automatically assigned a unique integer value, which you can retrieve with sqlite3_last_insert_rowid. You can then use that ID when inserting records into your "satellite" tables. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
