And of course you will need an index on edges.parent and one on edges.child since you need indexes on foreign keys. (unless you do not intend to enforce them and they are merely for "documentation" of intent to perhaps have a consistent database mayhaps perchance).
You will probably also want a unique constraint (index) on edges (parent, chaild) so you do not have multiple edges going from the same parent to the same child. You might also need a trigger to make sure that you do not have "duplicate" edges if your edges are "bidirectional" rather than directional. This will probably require a unique (child, parent) index as well. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Tuesday, 19 December, 2017 23:50 >To: SQLite mailing list >Subject: Re: [sqlite] Can we create a table where the value of one >particular column <> another column? > > > >On 20 Dec 2017, at 6:30am, Shane Dev <devshan...@gmail.com> wrote: > >> Let's say we have nodes and edges tables - >> >> sqlite> .sch nodes >> CREATE TABLE nodes(id integer primary key, description text); >> sqlite> .sch edges >> CREATE TABLE edges(parent references nodes, child references >nodes); >> >> Can we restrict the edges table so that inserting or updating a row >where >> edges.parent = edges.child is not allowed and ideally would produce >an >> error message? >> >> sqlite> insert into nodes select 1, 'node1'; >> sqlite> insert into nodes select 2, 'node2'; >> sqlite> insert into edges select 1, 2; >> sqlite> insert into edges select 1, 1; >> -- should be an error here > >Yes ! > >CREATE TABLE edges( > parent INTEGER references nodes, > child INTEGER references nodes, > CONSTRAINT NotMyOwnGrandpa CHECK (child <> parent) >); > >You’ll get a failure result code from the INSERT. > >Simon. >_______________________________________________ >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
