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

Reply via email to