Are self-referencing or recursive foreign keys allowed in sqlite? Is there a special syntax to accomplish this? So far I've tried the following to no avail: FOREIGN KEY(ParentPrimaryKeyId) REFERENCES ThisTableName(PrimaryKeyId)
For reference, I'm targeting sqlite 3.6.22 in iOS 4.
Yes sqlite supports self-referencing foreign keys, for example:
sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE SomeTable (
...> id INTEGER PRIMARY KEY AUTOINCREMENT,
...> parent_id INTEGER,
...> FOREIGN KEY(parent_id) REFERENCES SomeTable(id));
sqlite> INSERT INTO SomeTable (parent_id) VALUES (234324);
Error: foreign key constraint failed
sqlite> INSERT INTO SomeTable (parent_id) VALUES (NULL);
sqlite> SELECT * FROM SomeTable;
1|
sqlite> INSERT INTO SomeTable (parent_id) VALUES (1);
sqlite> SELECT * FROM SomeTable;
1|
2|1
sqlite>
SQLite supports foreign keys in which the referencing table and the referenced table are the same table. (See sixfeetsix's answer, for one. The target column of a foreign key constraint has to have a 'primary key' or 'unique' constraint on it.) But you should think hard about whether you actually need to store information about things and about the relationship between things in the same table.
For "parents" and "children", for example, something along these lines is often a better idea.
pragma foreign_keys = on;
create table persons (
person_id integer primary key,
person_name varchar(15) not null
);
insert into persons values (1, 'Dad');
insert into persons values (2, 'One son');
insert into persons values (3, 'One daughter');
create table persons_children (
parent_id integer references persons (person_id),
child_id integer references persons (person_id),
check (parent_id <> child_id),
primary key (parent_id, child_id)
);
insert into persons_children values (1,2);
insert into persons_children values (1,3);
Information about persons in one table; information about their relationship in another. Then retrieve the names by
select pc.parent_id, p1.person_name as parent_name,
pc.child_id, p2.person_name as child_name
from persons_children pc
inner join persons p1 on (p1.person_id = pc.parent_id)
inner join persons p2 on (p2.person_id = pc.child_id);
1 Dad 2 One son
1 Dad 3 One daughter
SQLite doesn't support recursive queries in the sense that, say, PostgreSQL does.
