Hello Igor, thank you for the information, it is a big help. > 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?
They way I think about a database is that you subdivide the data based on how you may want to retrieve it later. The main table is structure, and all of it's fields are mandatory not null (each record is a chemical structure). The other tables contain other data (about the chemical structure), such as available vendors, prices, experimentally measured values, and computer generated data. These fields may be null. Some of the other tables are fairly large (500-2500 cols), so I thought it would help make the query process more efficient if you could just search on the tables with the data you need and ignore others. If I am incorrect in thinking about the setup in this way, I would appreciate knowing about that. > 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. I guess what I was thinking was that tables should not have independent primary keys if there is a 1:1 relationship in the data between the tables. The way I was thinking about this is that the primary key value assigned to a record when it was inserted to the first table would be copied and used to insert into the second table, etc. In my spreadsheet way of thinking, that is having a single primary key that is used in multiple tables. I'm trying to learn to think "database" and not "spreadsheet". > 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. Later on, when I need to lookup data from a record using the 4 key values, there would have to be a way to retrieve the unique integer value ROWID that corresponds to the 4 keys (was assigned by AUTOINCREMENT). If I use unique like above, how would the lookup on the 4 keys work? LMHmedchem -- View this message in context: http://sqlite.1065341.n5.nabble.com/inserting-record-data-into-mutliple-tables-with-a-composite-primary-key-tp64874p64902.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
