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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
