Dan Kennedy <danielk1...@...> writes: > > > why returns the "INSERT INTO a" not an error while the "INSERT INTO > > b" does? How > > corresponds this behaviour to the concept of FOREIGN KEYS? > > > CREATE TABLE a( > > id INTEGER PRIMARY > > KEY > > AUTOINCREMENT, > > id_staff_editor INTEGER NOT NULL, > > FOREIGN KEY(id_staff_editor) REFERENCES a(id) > > ); > > > > INSERT INTO a(id_staff_editor) VALUES(1); > > > > CREATE TABLE b( > > id INTEGER PRIMARY > > KEY > > AUTOINCREMENT, > > id_staff_editor INTEGER NOT NULL, > > FOREIGN KEY(id_staff_editor) REFERENCES b(id) > > ); > > > > INSERT INTO b(id_staff_editor) VALUES(2); > > The key statement is in the second paragraph here: > > http://www.sqlite.org/foreignkeys.html#fk_deferred > > "If a statement modifies the contents of the database so > that an immediate foreign key constraint is in violation > at the conclusion the statement, an exception is thrown > and the effects of the statement are reverted." > > At the conclusion of your two insert statements, table "a" > contains (1, 1) and table "b" contains (1, 2). Since the > contents of table "b" violate the FK constraint, an > exception is thrown. > > Dan. >
Please believe me I really try hard to understand - but what I don't understand is the fact that you can insert in this case: PRAGMA foreign_keys = ON; /* **success INSERT = yes */ CREATE TABLE a( id INTEGER PRIMARY KEY AUTOINCREMENT, id_staff_editor INTEGER NOT NULL, FOREIGN KEY(id_staff_editor) REFERENCES a(id) ); INSERT INTO a(id_staff_editor) VALUES(1); The table is empty, there is no record and as far as I understand I'm doing the following Insert into the field id_staff_editor of table a the value 1 if the value 1 is already present in the field id of table a (but in the moment there is no record in table a) To my mind the concept of Foreign Keys means that you check before INSERT/UPDATE Oliver _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users