Now I using the following - CREATE TABLE nodes(id integer primary key, description text); CREATE TABLE edges(parent references nodes not null, child references nodes not null check (parent<>child), primary key(parent, child));
This seems to prevent the insertion of duplicate and parent=child records. I don't see any benefit in adding unique constraints or triggers. I want to allow a child to have more than one parents. I also created an index which will hopefully speed up filters on edges.child - CREATE INDEX iedges on edges(child, parent); Now I will try to enhance the statement to prevent cycles https://en.wikipedia.org/wiki/Cycle_(graph_theory)#Cycle_detection On 20 December 2017 at 08:23, Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
