For the specific case below I was just banging something out to test if it worked, so didn't adhere to the best design practices. All your comments are good ones for actual designs though.
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne Sent: Tuesday, December 13, 2016 2:50 PM To: SQLite mailing list Subject: Re: [sqlite] Foreign key child index question On Tue, Dec 13, 2016 at 8:07 PM, David Raymond <david.raym...@tomtom.com> wrote: > sqlite> create table parentTable > ...> ( > ...> id integer primary key, > ...> a int not null, > ...> b int not null, > ...> otherStuff, > ...> unique (a, b) > ...> ); > > sqlite> create table childTable > ...> ( > ...> id integer primary key, > ...> a int, --nullable > ...> b int, --nullable > ...> otherStuff, > ...> foreign key (a, b) references parentTable (a, b) > ...> ); > But why would you do that? If you have unique(a, b), that implies it's your natural key (NK), and id is "just" the surrogate key (SK). And the whole point of a surrogate key is to have lighter-weight (to store and index) and faster to match against foreigns keys (FKs). Otherwise you might as well set (a, b) as your PK, and not have an SK at all. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
