Re: [sqlite] nested foreign keys
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 [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Keith Medcalf [kmedc...@dessus.com] 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- >boun...@mailinglists.sqlite.org] 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 >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] 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- >boun...@mailinglists.sqlite.org] 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 >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] nested foreign keys
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 < roman.fleys...@einstein.yu.edu> wrote: > The statements work. Insertion fails. > > Roman > > > From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on > behalf of David Yip [dw...@peach-bun.com] > 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 < > roman.fleys...@einstein.yu.edu> 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 > > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nested foreign keys
The statements work. Insertion fails. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of David Yip [dw...@peach-bun.com] 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 < roman.fleys...@einstein.yu.edu> 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 > 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] 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 < roman.fleys...@einstein.yu.edu> 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 > 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
