FOREIGN KEY CASCADE ON UPDATE CONTAINING SOME NULL FIELDS AND SOME NON-NULL FIELDS NOT CASCADING [CORE4957] #5248
Comments
Modified by: Tim Kelly (m00bh000)description: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Whereas: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); ============ ============ SQL> SELECT * FROM Y; ============ ============ I can see how this problem probably originates: I think that NULLS should not be cascaded to the FK if all the => The following does not cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Whereas this does cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); ============ ============ SQL> SELECT * FROM Y; ============ ============ I can see how this problem probably originates, take a case of a single field foreign-key: SQL> CREATE TABLE X (A INTEGER ); ============ SQL> select * from x: ============ In the above case the cascade doesn't travel to the FK since the FK is currently NULL. This is by design, since essentially all the fields in the FK are currently NULL. But I would argue that in the case where multiple keys are used where at least one field in the FK is not null then the cascade should take place. |
Modified by: Tim Kelly (m00bh000)description: The following does not cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Whereas this does cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); ============ ============ SQL> SELECT * FROM Y; ============ ============ I can see how this problem probably originates, take a case of a single field foreign-key: SQL> CREATE TABLE X (A INTEGER ); ============ SQL> select * from x: ============ In the above case the cascade doesn't travel to the FK since the FK is currently NULL. This is by design, since essentially all the fields in the FK are currently NULL. But I would argue that in the case where multiple keys are used where at least one field in the FK is not null then the cascade should take place. => The following does not cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Whereas this does cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); ============ ============ SQL> SELECT * FROM Y; ============ ============ I can see how this problem probably originates, take a case of a single field foreign-key: SQL> CREATE TABLE X (A INTEGER ); ============ SQL> select * from x: ============ In the above case the cascade doesn't travel to the FK since the FK is currently NULL. This is by design, since essentially all the fields in the FK are currently NULL so the FK is inactive. But in the case where multiple keys are used where at least one field in the FK is not null then shouldn't the cascade should take place? summary: FOREIGN KEY CASCADE CONTAINING NULL COLUMNS NOT CASCADING => FOREIGN KEY CASCADE ON UPDATE CONTAINING SOME NULL COLUMNS NOT CASCADING |
Modified by: Tim Kelly (m00bh000)description: The following does not cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Whereas this does cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); ============ ============ SQL> SELECT * FROM Y; ============ ============ I can see how this problem probably originates, take a case of a single field foreign-key: SQL> CREATE TABLE X (A INTEGER ); ============ SQL> select * from x: ============ In the above case the cascade doesn't travel to the FK since the FK is currently NULL. This is by design, since essentially all the fields in the FK are currently NULL so the FK is inactive. But in the case where multiple keys are used where at least one field in the FK is not null then shouldn't the cascade should take place? => The following does not cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Whereas this does cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); ============ ============ SQL> SELECT * FROM Y; ============ ============ I can see how this problem probably originates, take a case of a single field foreign-key: SQL> CREATE TABLE X (A INTEGER ); ============ SQL> select * from x: ============ In the above case the cascade doesn't travel to the FK since the FK is currently NULL. This is by design, since essentially all the fields in the FK are currently NULL so the FK is inactive. But in the case where multiple keys are used where at least one field in the FK is not null then shouldn't the cascade take place? |
Modified by: Tim Kelly (m00bh000)summary: FOREIGN KEY CASCADE ON UPDATE CONTAINING SOME NULL COLUMNS NOT CASCADING => FOREIGN KEY CASCADE ON UPDATE CONTAINING SOME NULL FIELDS AND SOME NON-NULL FIELDS NOT CASCADING |
Modified by: Tim Kelly (m00bh000)description: The following does not cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Whereas this does cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); ============ ============ SQL> SELECT * FROM Y; ============ ============ I can see how this problem probably originates, take a case of a single field foreign-key: SQL> CREATE TABLE X (A INTEGER ); ============ SQL> select * from x: ============ In the above case the cascade doesn't travel to the FK since the FK is currently NULL. This is by design, since essentially all the fields in the FK are currently NULL so the FK is inactive. But in the case where multiple keys are used where at least one field in the FK is not null then shouldn't the cascade take place? => The following does not cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Whereas this does cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); ============ ============ SQL> SELECT * FROM Y; ============ ============ I can see how this problem probably originates, take a case of a single field foreign-key: SQL> CREATE TABLE X (A INTEGER ); ============ SQL> select * from x: ============ In the above case the cascade doesn't travel to the FK since the FK is currently NULL. This is by design, since essentially all the fields in the FK are currently NULL so the FK is inactive. But in the case where multiple keys are used where at least one field in the FK is not null then shouldn't the cascade take place? If this is by design then I question the design since in my first example it is now impossible to alter B in either X or Y where A = 1; |
Modified by: Tim Kelly (m00bh000)description: The following does not cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Whereas this does cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); ============ ============ SQL> SELECT * FROM Y; ============ ============ I can see how this problem probably originates, take a case of a single field foreign-key: SQL> CREATE TABLE X (A INTEGER ); ============ SQL> select * from x: ============ In the above case the cascade doesn't travel to the FK since the FK is currently NULL. This is by design, since essentially all the fields in the FK are currently NULL so the FK is inactive. But in the case where multiple keys are used where at least one field in the FK is not null then shouldn't the cascade take place? If this is by design then I question the design since in my first example it is now impossible to alter B in either X or Y where A = 1; => The following does not cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Whereas this does cascade: SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); ============ ============ SQL> SELECT * FROM Y; ============ ============ I can see how this problem probably originates, take a case of a single field foreign-key: SQL> CREATE TABLE X (A INTEGER ); ============ SQL> select * from x: ============ In the above case the cascade doesn't travel to the FK since the FK is currently NULL. This is by design, since essentially all the fields in the FK are currently NULL so the FK is inactive. But in the case where multiple keys are used where at least one field in the FK is not null then shouldn't the cascade take place? If this is by design then I question the design since in my first example it is now impossible to alter B in either X or Y or both where A = 1; |
|
Commented by: Tim Kelly (m00bh000) I though I'd see how this scenario is handled in postgres: Tim=# CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER); Tim=# SELECT * FROM X; Tim=# SELECT * FROM Y; So it doesn't cascade, but the FK isn't enforced. Not ideal but at least it is possible to update B. |
|
Commented by: Tim Kelly (m00bh000) Having considered this further. I would summarise that the current behaviour is that a record bound by a foreign key containing at least one field that is not null and other fields that are null is enforced (ie a link to the unique key exists) but a cascade is not triggered if the index pointed at changes. It seems to me that this is a contradiction. The key is being enforced as if the two records are linked but cascades aren't. I would argue that a record bound to a FK containing at least one NULL should either: A) effectively exclude that record from the constraint completely (ie it doesn't point to any particular record in the Unique key), and consequently no cascade should take place. Therefore in my example: X(1, 2), Y(1,NULL) is allowed. or B) The bind should be enforced and the cascade should work. X(1,NULL), Y(1,NULL), change X to (1,2) and Y should automatically change to (1,2). Nb. In PostgreSQL, option A is taken. |
Modified by: Tim Kelly (m00bh000)Version: 3.0 RC 1 [ 10584 ] Version: 3.0 Beta 2 [ 10586 ] => |
|
Commented by: @kattunga I have the same problem, so I had to use triggers to perform logic control. |
Submitted by: Tim Kelly (m00bh000)
Votes: 3
The following does not cascade:
SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER);
SQL> CREATE TABLE Y (A INTEGER PRIMARY KEY, B INTEGER);
SQL> ALTER TABLE X ADD UNIQUE(A,B);
SQL> ALTER TABLE Y ADD FOREIGN KEY (A,B) REFERENCES X(A,B) ON UPDATE CASCADE;
SQL> INSERT INTO X VALUES(1,NULL);
SQL> INSERT INTO Y VALUES(1,NULL);
SQL> UPDATE X SET B = 1 WHERE A = 1;
Statement failed, SQLSTATE = 23000
violation of FOREIGN KEY constraint "INTEG_3304" on table "Y"
-Foreign key references are present for the record
-Problematic key value is ("A" = 1, "B" = NULL)
Whereas this does cascade:
SQL> CREATE TABLE X (A INTEGER PRIMARY KEY, B INTEGER);
SQL> CREATE TABLE Y (A INTEGER PRIMARY KEY, B INTEGER);
SQL> ALTER TABLE X ADD UNIQUE(A,B);
SQL> ALTER TABLE Y ADD FOREIGN KEY (A,B) REFERENCES X(A,B) ON UPDATE CASCADE;
SQL> INSERT INTO X VALUES(1,0);
SQL> INSERT INTO Y VALUES(1,0);
SQL> UPDATE X SET B = 1 WHERE A = 1;
SQL> SELECT * FROM X;
============ ============
1 1
SQL> SELECT * FROM Y;
============ ============
1 1
I can see how this problem probably originates, take a case of a single field foreign-key:
SQL> CREATE TABLE X (A INTEGER );
SQL> CREATE TABLE Y (A INTEGER );
SQL> ALTER TABLE X ADD UNIQUE(A);
SQL> ALTER TABLE Y ADD FOREIGN KEY (A) REFERENCES X(A) ON UPDATE CASCADE;
SQL> INSERT INTO X VALUES(NULL);
SQL> INSERT INTO Y VALUES(NULL);
SQL> UPDATE X SET A = 2;
SQL>
SQL> select * from y;
============
<null>
SQL> select * from x:
============
2
In the above case the cascade doesn't travel to the FK since the FK is currently NULL. This is by design, since essentially all the fields in the FK are currently NULL so the FK is inactive.
But in the case where multiple keys are used where at least one field in the FK is not null then shouldn't the cascade take place?
If this is by design then I question the design since in my first example it is now impossible to alter B in either X or Y or both where A = 1;
The text was updated successfully, but these errors were encountered: