Quoth Yves Goergen <[email protected]>, on 2011-01-03 13:01:17 +0100: > So I have foreign keys from message_revision to message and the other > way around. This obviously won't work because when defining the table > message, the table message_revision isn't known yet.
What do you mean? Creating two tables with circular foreign keys works fine for me. I haven't tried your tables specifically; if you get an error for them, could you please describe it in detail along with how you tried to create the tables and any subsequent queries? sqlite> pragma foreign_keys=on; sqlite> create table a (id integer not null primary key, bx integer null references b (id)); sqlite> create table b (id integer not null primary key, ax integer null references a (id)); sqlite> insert into a (id) values(3); sqlite> insert into b (id) values(5); sqlite> update a set bx = -42 where id = 3; Error: foreign key constraint failed sqlite> update a set bx = 5 where id = 3; sqlite> update b set ax = 109 where id = 5; Error: foreign key constraint failed sqlite> insert into a (id, bx) values (109, 5); sqlite> update b set ax = 109 where id = 5; sqlite> insert into a (id, bx) values (110, -3); Error: foreign key constraint failed In particular, if you never create table B, subsequent operations on A may fail, but the creation succeeds and allows you to create B later. Also, dropping the tables may be awkward unless you turn foreign keys off first, but... ---> Drake Wilson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
