пятница, 30 сентября 2016 г.

Foreign keys and nulls

Be careful with null enabled foreign key, especially multicolumn.
Test case below:

CREATE TABLE par(id1 NUMBER NOT NULL, id2 NUMBER NOT NULL, CONSTRAINT par_pk PRIMARY KEY(id1, id2));
Table created
INSERT INTO par VALUES(1,1);
1 row inserted
CREATE TABLE chi(id1 NUMBER, id2 NUMBER, CONSTRAINT par_fk FOREIGN KEY (id1, id2) REFERENCES par(id1, id2));
Table created
INSERT INTO chi VALUES(NULL, NULL);-- double nulls are allowed
1 row inserted
INSERT INTO chi VALUES(1, NULL);
1 row inserted
INSERT INTO chi VALUES(2, NULL);--!!!we have no 2 in par table!!!
1 row inserted
INSERT INTO chi VALUES(2, 1);
INSERT INTO chi VALUES(2, 1)
ORA-02291: integrity constraint (DOCKER.PAR_FK) violated - parent key not found

If one of the fields is null, the second field doesn’t need to present in parent table.

Комментариев нет: