Can we create a table where the value of one particular column <> another column?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
Use a trigger and make it raise an exception, something like (not tested, just an example of how it might work):

CREATE TRIGGER <name> BEFORE INSERT ON <table> WHEN NEW.parent == NEW.child BEGIN RAISE(FAIL, 'connecting to self not allowed') END;

You may also require a similar BEFORE UPDATE trigger to avoid changes that violate the condition.


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Shane Dev
Gesendet: Mittwoch, 20. Dezember 2017 07:30
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Can we create a table where the value of one particular column <> another column?

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'; insert into nodes select 2,
sqlite> 'node2'; insert into edges select 1, 2; insert into edges select
sqlite> 1, 1;
-- should be an error here
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by Shane Dev


On 20 Dec 2017, at 6:30am, Shane Dev <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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-
>[hidden email]] 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 <[hidden email]> 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Shane Dev
In reply to this post by Simon Slavin-3
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Shane Dev
In reply to this post by Keith Medcalf
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 <[hidden email]> 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-
> >[hidden email]] 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 <[hidden email]> 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
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users