Hello, 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. 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. 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. 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. That assumes that there would be a way to look up the rowid associated with a set of 4 key values and use that to retrieve data from any table where that rowid was used to insert data. Am I going about this in the best way, or even in a reasonable way? Suggestions and criticisms would be appreciated and a link to some examples or a tutorial would be fantastic. LMHmedchem -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-datinto-mutliple-tables-with-a-composite-primary-key-tp64874.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
