Re: [sqlite] Can we create a table where the value of one particular column <> another column?

[Shane Dev]

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

Re: [sqlite] Can we create a table where the value of one particular column <> another column?

[Shane Dev]

Nice solution!

CREATE TABLE edges(parent references nodes, child references nodes check
(parent<>child));

seems to be an equivalent but shorter statement.

On 20 December 2017 at 07:49, Simon Slavin  wrote:

>
>
> 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

Re: [sqlite] Can we create a table where the value of one particular column <> another column?

[Keith Medcalf]

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

Re: [sqlite] Can we create a table where the value of one particular column <> another column?

[Simon Slavin]

On 20 Dec 2017, at 6:30am, Shane Dev  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] Can we create a table where the value of one particular column <> another column?

[Shane Dev]

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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users