On Tuesday, 16 October, 2012, 11:51, LMHmedchem said:
You could always create a table JUST for for the compsite key which gives you
JUST the single-key which you then use to look up data in the other tables. If
you specifically name the rowid columns, then you can use simple and efficient
joins to retrieve the data from the subtables:
Create table masterkey(
rowkey integer primary key autoincrement,
k1 integer not null,
k2 integer not null,
k3 integer not null,
k4 integer not null,
unique (k1, k2, k3, k4));
create table sub1(
rowkey integer primary key references masterkey(rowkey),
...);
...
You then join the masterkey table to whatever subs you want to do the
retrieval, and when storing data you store the masterkey first and use the
rowkey (last_insert_rowid) for the inserts in the subtables.
Select * from masterkey, sub1 where k1=? And k2=? And k3=? And k4=? And
masterkey.rowkey=sub1.rowkey;
If you are careful about your column names you could also just use a NATURAL
JOIN to join the rowkey fields and not need the explicit equijoins on the
rowkey in the select.
On all inserts you would first do the same operation against the masterkey
table to get the appropriate rowkey -- first a lookup then an insert of the key
if required.
> 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
>
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
