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
