nested foreign keys

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

nested foreign keys

Roman Fleysher
Dear SQLiters,

I am trying to set up what I would call "nested foreign keys":

create grandParent (id PRIMARY KEY )
create parent (id PRIMARY KEY REFERENCES grandParent(id))
create child (id PRIMARY KEY REFERENCES parent(id))

SQLite complains. Does it mean that grand children are not allowed?

Thank you,

Roman
_______________________________________________
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: nested foreign keys

David Yip
These statements worked for me:


CREATE TABLE grandparent (id INTEGER PRIMARY KEY);

CREATE TABLE parent (id INTEGER PRIMARY KEY REFERENCES grandparent(id));

CREATE TABLE child (id INTEGER PRIMARY KEY REFERENCES parent(id));


The foreign key constraints work as you'd expect also.


What are you doing and what error are you seeing?


- David

On Wed, Oct 24, 2018 at 11:30 PM Roman Fleysher <
[hidden email]> wrote:

> Dear SQLiters,
>
> I am trying to set up what I would call "nested foreign keys":
>
> create grandParent (id PRIMARY KEY )
> create parent (id PRIMARY KEY REFERENCES grandParent(id))
> create child (id PRIMARY KEY REFERENCES parent(id))
>
> SQLite complains. Does it mean that grand children are not allowed?
>
> Thank you,
>
> Roman
> _______________________________________________
> 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: nested foreign keys

Roman Fleysher
The statements work. Insertion fails.

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of David Yip [[hidden email]]
Sent: Thursday, October 25, 2018 12:37 AM
To: SQLite mailing list
Subject: Re: [sqlite] nested foreign keys

These statements worked for me:


CREATE TABLE grandparent (id INTEGER PRIMARY KEY);

CREATE TABLE parent (id INTEGER PRIMARY KEY REFERENCES grandparent(id));

CREATE TABLE child (id INTEGER PRIMARY KEY REFERENCES parent(id));


The foreign key constraints work as you'd expect also.


What are you doing and what error are you seeing?


- David

On Wed, Oct 24, 2018 at 11:30 PM Roman Fleysher <
[hidden email]> wrote:

> Dear SQLiters,
>
> I am trying to set up what I would call "nested foreign keys":
>
> create grandParent (id PRIMARY KEY )
> create parent (id PRIMARY KEY REFERENCES grandParent(id))
> create child (id PRIMARY KEY REFERENCES parent(id))
>
> SQLite complains. Does it mean that grand children are not allowed?
>
> Thank you,
>
> Roman
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: nested foreign keys

David Yip
What do your inserts look like?  This SQL will function as expected:

    CREATE TABLE grandparent (id INTEGER PRIMARY KEY);
    CREATE TABLE parent (id INTEGER PRIMARY KEY REFERENCES grandparent(id));
    CREATE TABLE child (id INTEGER PRIMARY KEY REFERENCES parent(id));

    INSERT INTO grandparent VALUES (1);
    INSERT INTO parent VALUES (1);
    INSERT INTO child VALUES (1);

You can mix up the insertion order if you defer checking (
https://www.sqlite.org/foreignkeys.html#fk_deferred); if not, you'll have
to insert parents before children.

- David

On Wed, Oct 24, 2018 at 11:44 PM Roman Fleysher <
[hidden email]> wrote:

> The statements work. Insertion fails.
>
> Roman
>
> ________________________________________
> From: sqlite-users [[hidden email]] on
> behalf of David Yip [[hidden email]]
> Sent: Thursday, October 25, 2018 12:37 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] nested foreign keys
>
> These statements worked for me:
>
>
> CREATE TABLE grandparent (id INTEGER PRIMARY KEY);
>
> CREATE TABLE parent (id INTEGER PRIMARY KEY REFERENCES grandparent(id));
>
> CREATE TABLE child (id INTEGER PRIMARY KEY REFERENCES parent(id));
>
>
> The foreign key constraints work as you'd expect also.
>
>
> What are you doing and what error are you seeing?
>
>
> - David
>
> On Wed, Oct 24, 2018 at 11:30 PM Roman Fleysher <
> [hidden email]> wrote:
>
> > Dear SQLiters,
> >
> > I am trying to set up what I would call "nested foreign keys":
> >
> > create grandParent (id PRIMARY KEY )
> > create parent (id PRIMARY KEY REFERENCES grandParent(id))
> > create child (id PRIMARY KEY REFERENCES parent(id))
> >
> > SQLite complains. Does it mean that grand children are not allowed?
> >
> > Thank you,
> >
> > Roman
> > _______________________________________________
> > 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
>
_______________________________________________
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: nested foreign keys

Keith Medcalf
In reply to this post by Roman Fleysher

No, it means that you did not specify the whatisness of grandParent, parent, or child; and/or, you have not enabled foreign_keys.

https://sqlite.org/lang_createtable.html
https://sqlite.org/pragma.html#pragma_foreign_keys

NB:  I have compiled the CLI with foreign key enforcement ON be default.  The default distributions usually have foreign keys enforcement turned off, because, well, who wants a database that enforces referential integrity?  (All kidding aside, the reason that foreign key enforcement is OFF by default is to maintain backward compatibility with older versions of SQLite that "parsed" such constraints but did not allow for enforcement of them).

SQLite version 3.26.0 2018-10-23 13:48:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table grandParent (id PRIMARY KEY );
sqlite> create table parent (id PRIMARY KEY REFERENCES grandParent(id));
sqlite> create table child (id PRIMARY KEY REFERENCES parent(id));
sqlite> insert into parent values (1);
Error: FOREIGN KEY constraint failed
sqlite> insert into child values (1);
Error: FOREIGN KEY constraint failed
sqlite> insert into grandparent values (1);
sqlite> insert into parent values (1);
sqlite> insert into child values (1);
sqlite> delete from parent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from grandparent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from child where id=1;
sqlite> delete from grandparent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from parent where id=1;
sqlite> delete from grandparent where id=1;
sqlite>

---
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 Roman Fleysher
>Sent: Wednesday, 24 October, 2018 22:30
>To: General Discussion of SQLite Database
>Subject: [sqlite] nested foreign keys
>
>Dear SQLiters,
>
>I am trying to set up what I would call "nested foreign keys":
>
>create grandParent (id PRIMARY KEY )
>create parent (id PRIMARY KEY REFERENCES grandParent(id))
>create child (id PRIMARY KEY REFERENCES parent(id))
>
>SQLite complains. Does it mean that grand children are not allowed?
>
>Thank you,
>
>Roman
>_______________________________________________
>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: nested foreign keys

Roman Fleysher
I found the cause of my issue.

I have two columns as foreign key in child, which reference corresponding pair in parent. But, I was setting up the references separately, not as a pair. And it looked like it can not work.

It works because the pair of columns in parent is PRIMARY KEY and thus has unique index -- the only requirement for foreign keys to work.

Roman


________________________________________
From: sqlite-users [[hidden email]] on behalf of Keith Medcalf [[hidden email]]
Sent: Thursday, October 25, 2018 1:03 AM
To: SQLite mailing list
Subject: Re: [sqlite] nested foreign keys

No, it means that you did not specify the whatisness of grandParent, parent, or child; and/or, you have not enabled foreign_keys.

https://sqlite.org/lang_createtable.html
https://sqlite.org/pragma.html#pragma_foreign_keys

NB:  I have compiled the CLI with foreign key enforcement ON be default.  The default distributions usually have foreign keys enforcement turned off, because, well, who wants a database that enforces referential integrity?  (All kidding aside, the reason that foreign key enforcement is OFF by default is to maintain backward compatibility with older versions of SQLite that "parsed" such constraints but did not allow for enforcement of them).

SQLite version 3.26.0 2018-10-23 13:48:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table grandParent (id PRIMARY KEY );
sqlite> create table parent (id PRIMARY KEY REFERENCES grandParent(id));
sqlite> create table child (id PRIMARY KEY REFERENCES parent(id));
sqlite> insert into parent values (1);
Error: FOREIGN KEY constraint failed
sqlite> insert into child values (1);
Error: FOREIGN KEY constraint failed
sqlite> insert into grandparent values (1);
sqlite> insert into parent values (1);
sqlite> insert into child values (1);
sqlite> delete from parent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from grandparent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from child where id=1;
sqlite> delete from grandparent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from parent where id=1;
sqlite> delete from grandparent where id=1;
sqlite>

---
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 Roman Fleysher
>Sent: Wednesday, 24 October, 2018 22:30
>To: General Discussion of SQLite Database
>Subject: [sqlite] nested foreign keys
>
>Dear SQLiters,
>
>I am trying to set up what I would call "nested foreign keys":
>
>create grandParent (id PRIMARY KEY )
>create parent (id PRIMARY KEY REFERENCES grandParent(id))
>create child (id PRIMARY KEY REFERENCES parent(id))
>
>SQLite complains. Does it mean that grand children are not allowed?
>
>Thank you,
>
>Roman
>_______________________________________________
>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