Basic Foreign Key
SQL Server Foreign Keys normally behave well and as expected, however sometimes they behave badly and in unexpected ways.
I will first show the normal good behavior, then the bad behavior. Plus, provide some solutions for the bad behavior.
First, create 2 tables - Table_A and Table_B.
CREATE TABLE dbo.Table_A
(
A_ID INT NOT NULL,
A_Name varchar(50) NOT NULL,
CONSTRAINT PK_Table_A PRIMARY KEY (A_ID)
);
CREATE TABLE dbo.Table_B
(
B_ID INT NOT NULL,
B_Name VARCHAR(50) NOT NULL,
A_ID INT NOT NULL,
CONSTRAINT PK_Table_B PRIMARY KEY (B_ID)
);
Now define a Foreign Key (FK) relationship between the 2 tables. Table_A is the Primary Key Table and Table_B is the Foreign Key Table.
ALTER TABLE dbo.Table_B ADD CONSTRAINT FK_Table_B_Table_A FOREIGN KEY (A_ID) REFERENCES dbo.Table_A (A_ID);
Insert a test record into Table_A.
INSERT dbo.Table_A (A_ID, A_Name) VALUES (7, 'Seven');
Given the test record in the Primary Key Table, what do you expect the results of the following test inserts to be? Will there be an error? Is the INSERT allowed? Is the data after the attempted test valid (if an error occurred then the data remains valid).
INSERT Tests (Table_B with FK to Table_A. FK column disallows nulls.)
| Test |
B_ID |
B_Name |
A_ID |
Error? |
Insert? |
Valid? |
| #1 |
1 |
One |
7 |
? |
? |
? |
| #2 |
2 |
Two |
123 |
? |
? |
? |
| #3 |
3 |
Three |
NULL |
? |
? |
? |
Test #1:
INSERT dbo.Table_B (B_ID, B_Name, A_ID) VALUES (1, 'One', 7);
Results:
(1 row(s) affected)
This is the expected result. The FK constraint was statisfied. No error occurred. The insert was allowed. The data after the insert remains valid.
Test #2:
INSERT dbo.Table_B (B_ID, B_Name, A_ID) VALUES (2, 'Two', 12345);
Results:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Table_B_Table_A".
The conflict occurred in database "test", table "dbo.Table_A", column 'A_ID'.
The statement has been terminated.
Again, this is the expected result. The FK constraint was violated. An error occurred. The insert was not allowed. The data remains valid.
Test #3:
INSERT dbo.Table_B (B_ID, B_Name, A_ID) VALUES (3, 'Three', NULL);
Results:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'A_ID', table 'test.dbo.Table_B';
column does not allow nulls. INSERT fails.
The statement has been terminated.
This is the expected result. The FK column in Table_B does not allow nulls. An error occurred. The insert was not allowed. The data remains valid.
Filling in the table from above you get the expected normal good behavior of a Foreign Key. For additional confirmation you can query Table_B to view that the correct rows (in this case only one) were inserted - the data remains valid.
INSERT Tests (Table_B with FK to Table_A. FK column disallows nulls.)
| Test |
B_ID |
B_Name |
A_ID |
Error? |
Insert? |
Valid? |
| #1 |
1 |
One |
7 |
N |
Y |
Y |
| #2 |
2 |
Two |
123 |
Y |
N |
Y |
| #3 |
3 |
Three |
NULL |
Y |
N |
Y |
Nullable Foreign Key
A Foreign Key can be defined on a column that allow nulls. If Table_B had been created using the following statement (note that the FK column A_ID allows nulls), Test #3 above would have resulted in no error, the successful insertion of the record, and the data would still be valid.
CREATE TABLE dbo.Table_B
(
B_ID INT NOT NULL,
B_Name VARCHAR(50) NOT NULL,
A_ID INT NULL, -- allow nulls
CONSTRAINT PK_Table_B PRIMARY KEY (B_ID)
);
Again you get the expect normal behavior of a Foreign Key with the small variation due to the nullability of the FK column.
INSERT Tests (Table_B with FK to Table_A. FK column allows nulls.)
| Test |
B_ID |
B_Name |
A_ID |
Error? |
Insert? |
Valid? |
| #1 |
1 |
One |
7 |
N |
Y |
Y |
| #2 |
2 |
Two |
123 |
Y |
N |
Y |
| #3 |
3 |
Three |
NULL |
N |
Y |
Y |
For additional confirmation you could query this second version of Table_B to view that the correct rows were inserted (in this case two rows) - the data remains valid.
Composite Foreign Key
A Foreign Key can also be a composite Foreign Key. That is, made up of more that one column. The corresponding Primary Key (or Unique Constraint) is also composite or multiple columns.
Let's create 2 more tables: Table_C and Table_D. Table_C will have a composite PK. Table_D will have a corresponding composite FK. The first version of Table_D will define the FK columns as disallowing nulls.
CREATE TABLE dbo.Table_C
(
C_ID1 INT NOT NULL,
C_ID2 INT NOT NULL,
C_Name varchar(50) NOT NULL,
CONSTRAINT PK_Table_C PRIMARY KEY (C_ID1, C_ID2)
);
CREATE TABLE dbo.Table_D
(
D_ID INT NOT NULL,
D_Name VARCHAR(50) NOT NULL,
C_ID1 INT NOT NULL,
C_ID2 INT NOT NULL,
CONSTRAINT PK_Table_D PRIMARY KEY (D_ID)
);
Now define a Foreign Key (FK) relationship between the 2 tables. Table_C is the Primary Key Table and Table_D is the Foreign Key Table.
ALTER TABLE dbo.Table_D ADD CONSTRAINT FK_Table_D_Table_C FOREIGN KEY (C_ID1, C_ID2) REFERENCES dbo.Table_C (C_ID1, C_ID2);
Insert a couple of test records into Table_C.
INSERT dbo.Table_C (C_ID1, C_ID2, C_Name) VALUES (7, 11, 'Seven Eleven');
INSERT dbo.Table_C (C_ID1, C_ID2, C_Name) VALUES (10, 4, 'Ten Four');
Given the test records in the Primary Key Table, what do you expect the results of the following test inserts to be? Will there be an error? Is the INSERT allowed? Is the data after the attempted test valid (if an error occurred then the data remains valid).
INSERT Tests (Table_D with composite FK to Table_C. FK columns disallows nulls.)
| Test |
D_ID |
D_Name |
C_ID1 |
C_ID2 |
Error? |
Insert? |
Valid? |
| #1 |
1 |
One |
7 |
11 |
? |
? |
? |
| #2 |
2 |
Two |
10 |
4 |
? |
? |
? |
| #3 |
3 |
Three |
11 |
7 |
? |
? |
? |
| #4 |
4 |
Four |
7 |
4 |
? |
? |
? |
| #5 |
5 |
Five |
10 |
11 |
? |
? |
? |
| #6 |
6 |
Six |
123 |
789 |
? |
? |
? |
| #7 |
7 |
Seven |
NULL |
NULL |
? |
? |
? |
| #8 |
8 |
Eight |
7 |
NULL |
? |
? |
? |
| #9 |
9 |
Nine |
NULL |
11 |
? |
? |
? |
| #10 |
10 |
Ten |
123 |
NULL |
? |
? |
? |
| #11 |
11 |
Eleven |
NULL |
789 |
? |
? |
? |
Note: There are other possible variations to test in addition to the above 11. These were chosen to show the desired behavior.
Perform the eleven tests listed below one-at-a-time and note the results.
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (1, 'One', 7, 11);
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (2, 'Two', 10, 4);
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (3, 'Three', 11, 7);
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (4, 'Four', 7, 4);
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (5, 'Five', 10, 11);
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (6, 'Six', 123, 789);
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (7, 'Seven', NULL, NULL);
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (8, 'Eight', 7, NULL);
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (9, 'Nine', NULL, 11);
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (10, 'Ten', 123, NULL);
INSERT dbo.Table_D (D_ID, D_Name, C_ID1, C_ID2) VALUES (11, 'Eleven', NULL, 789);
You should see the following results:
INSERT Tests (Table_D with composite FK to Table_C. FK columns disallow nulls.)
| Test |
D_ID |
D_Name |
C_ID1 |
C_ID2 |
Error? |
Insert? |
Valid? |
| #1 |
1 |
One |
7 |
11 |
N |
Y |
Y |
| #2 |
2 |
Two |
10 |
4 |
N |
Y |
Y |
| #3 |
3 |
Three |
11 |
7 |
Y |
N |
Y |
| #4 |
4 |
Four |
7 |
4 |
Y |
N |
Y |
| #5 |
5 |
Five |
10 |
11 |
Y |
N |
Y |
| #6 |
6 |
Six |
123 |
789 |
Y |
N |
Y |
| #7 |
7 |
Seven |
NULL |
NULL |
Y |
N |
Y |
| #8 |
8 |
Eight |
7 |
NULL |
Y |
N |
Y |
| #9 |
9 |
Nine |
NULL |
11 |
Y |
N |
Y |
| #10 |
10 |
Ten |
123 |
NULL |
Y |
N |
Y |
| #11 |
11 |
Eleven |
NULL |
789 |
Y |
N |
Y |
To confirm, if you retrieve the records from Table_D:
SELECT * FROM Table_D;
You should only see 2 rows:
| D_ID |
D_Name |
C_ID1 |
C_ID2 |
| 1 |
One |
7 |
11 |
| 2 |
Two |
10 |
4 |
No surprises here. These are the expected results for a Foreign Key. More good behavior. Kinda boring right?
Composite Nullable Foreign Key (Bad Behavior Begins!)
What about if the composite Foreign Key columns allow nulls? Here is what you have been waiting for. Here is where the intriguing stuff begins aka Foreign Key Bad Behavior.
Let's create a second Table_D called Table_D2 and make the FK columns allow nulls:
CREATE TABLE dbo.Table_D2
(
D_ID INT NOT NULL,
D_Name VARCHAR(50) NOT NULL,
C_ID1 INT NULL, -- allow nulls
C_ID2 INT NULL, -- allow nulls
CONSTRAINT PK_Table_D2 PRIMARY KEY (D_ID)
);
Define the FK Constraint:
ALTER TABLE dbo.Table_D2 ADD CONSTRAINT FK_Table_D2_Table_C FOREIGN KEY (C_ID1, C_ID2) REFERENCES dbo.Table_C (C_ID1, C_ID2);
Table_C still has the same 2 original test records - 7 + 11 and 10 + 4 in ID_C1 + ID_C2.
Perform the same tests as above against Table_D2. You might expect that the only difference would be with Test #7. That this insert would cause no error, the insert would be allowed, and the data after the insert would be valid. Your expections would be wrong. The actual results are very intriguing and potentially very problematic in a real-world solution!
INSERT Tests (Table_D2 with composite FK to Table_C. FK columns allow nulls.)
| Test |
D_ID |
D_Name |
C_ID1 |
C_ID2 |
Error? |
Insert? |
Valid? |
| #1 |
1 |
One |
7 |
11 |
? |
? |
? |
| #2 |
2 |
Two |
10 |
4 |
? |
? |
? |
| #3 |
3 |
Three |
11 |
7 |
? |
? |
? |
| #4 |
4 |
Four |
7 |
4 |
? |
? |
? |
| #5 |
5 |
Five |
10 |
11 |
? |
? |
? |
| #6 |
6 |
Six |
123 |
789 |
? |
? |
? |
| #7 |
7 |
Seven |
NULL |
NULL |
? |
? |
? |
| #8 |
8 |
Eight |
7 |
NULL |
? |
? |
? |
| #9 |
9 |
Nine |
NULL |
11 |
? |
? |
? |
| #10 |
10 |
Ten |
123 |
NULL |
? |
? |
? |
| #11 |
11 |
Eleven |
NULL |
789 |
? |
? |
? |
Perform the eleven tests listed below one-at-a-time and note the results.
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (1, 'One', 7, 11);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (2, 'Two', 10, 4);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (3, 'Three', 11, 7);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (4, 'Four', 7, 4);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (5, 'Five', 10, 11);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (6, 'Six', 123, 789);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (7, 'Seven', NULL, NULL);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (8, 'Eight', 7, NULL);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (9, 'Nine', NULL, 11);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (10, 'Ten', 123, NULL);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (11, 'Eleven', NULL, 789);
You should see the following results:
INSERT Tests (Table_D2 with composite FK to Table_C. FK columns allow nulls.)
| Test |
D_ID |
D_Name |
C_ID1 |
C_ID2 |
Error? |
Insert? |
Valid? |
| #1 |
1 |
One |
7 |
11 |
N |
Y |
Y |
| #2 |
2 |
Two |
10 |
4 |
N |
Y |
Y |
| #3 |
3 |
Three |
11 |
7 |
Y |
N |
Y |
| #4 |
4 |
Four |
7 |
4 |
Y |
N |
Y |
| #5 |
5 |
Five |
10 |
11 |
Y |
N |
Y |
| #6 |
6 |
Six |
123 |
789 |
Y |
N |
Y |
| #7 |
7 |
Seven |
NULL |
NULL |
N |
Y |
Y <=== Expected |
| #8 |
8 |
Eight |
7 |
NULL |
N |
Y |
N <=== Unexpected Bad Behavior |
| #9 |
9 |
Nine |
NULL |
11 |
N |
Y |
N <=== Unexpected Bad Behavior |
| #10 |
10 |
Ten |
123 |
NULL |
N |
Y |
N <=== Unexpected Bad Behavior!!! |
| #11 |
11 |
Eleven |
NULL |
789 |
N |
Y |
N <=== Unexpected Bad Behavior!!! |
To confirm, if you retrieve the records from Table_D:
SELECT * FROM Table_D2;
You see 7 rows:
| D_ID |
D_Name |
C_ID1 |
C_ID2 |
| 1 |
One |
7 |
11 |
| 2 |
Two |
10 |
4 |
| 7 |
Seven |
NULL |
NULL |
| 8 |
Eight |
7 |
NULL |
| 9 |
Nine |
NULL |
11 |
| 10 |
Ten |
123 |
NULL |
| 11 |
Eleven |
NULL |
789 |
The first three rows (D_ID = 1, 2, 7) are valid. The last four rows (D_ID = 8, 9, 10, 11) are invalid data. WTHeck!?! D_ID = 8 and 9 are semi-valid - at least the non-null value matches at least part of the PK. But D_ID = 10 and 11 are completely invalid. What is going on with the FK Constraint and with SQL Server? What is causing this bad behavior and what can we do about it?
Fixing the Bad Behavior
Microsoft gives some insight in the
SQL Server Books Online.
"A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns."
Note: I am sure Kalen Delaney or CJ Date or Joe Celko or the ANSI-SQL group members could explain what is really going on, but I will just benefit from their great efforts and press the "I believe" button and try and make things work.
Making all columns of a composite Foreign Key disallow nulls is clearly one option to eliminate the possibility of invalid data sneaking in through a composite Foreign Key with nullable columns. However if you must keep the Foreign Key columns nullable because of your database design there is another possible solution: Adding in a Check Constraint to block the bad behavior.
To do this let's first clear out Table_D2:
TRUNCATE TABLE Table_D2;
Next add a simple Check Constraint to Table_D2. The columns of the Foreign Key still allow nulls, but the new Check Constraint will ensure all columns of the composite FK are either all null or all not null - no more partial nullness (that is a real technical term - look it up <grin /> ). Since partial nullness is eliminated the skipping of the FK verification is stopped. The FK Bad Behavior should now be stopped.
ALTER TABLE dbo.Table_D2
ADD CONSTRAINT CK_Table_D2_FK_Table_C CHECK
((C_ID1 IS NULL AND C_ID2 IS NULL) OR (C_ID1 IS NOT NULL AND C_ID2 IS NOT NULL));
Now perform the Insert Tests again.
Perform the eleven tests listed below one-at-a-time and note the results.
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (1, 'One', 7, 11);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (2, 'Two', 10, 4);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (3, 'Three', 11, 7);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (4, 'Four', 7, 4);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (5, 'Five', 10, 11);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (6, 'Six', 123, 789);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (7, 'Seven', NULL, NULL);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (8, 'Eight', 7, NULL);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (9, 'Nine', NULL, 11);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (10, 'Ten', 123, NULL);
INSERT dbo.Table_D2 (D_ID, D_Name, C_ID1, C_ID2) VALUES (11, 'Eleven', NULL, 789);
You should see the following results:
INSERT Tests (Table_D2 with composite FK to Table_C. FK columns allow nulls.)
| Test |
D_ID |
D_Name |
C_ID1 |
C_ID2 |
Error? |
Insert? |
Valid? |
| #1 |
1 |
One |
7 |
11 |
N |
Y |
Y |
| #2 |
2 |
Two |
10 |
4 |
N |
Y |
Y |
| #3 |
3 |
Three |
11 |
7 |
Y |
N |
Y |
| #4 |
4 |
Four |
7 |
4 |
Y |
N |
Y |
| #5 |
5 |
Five |
10 |
11 |
Y |
N |
Y |
| #6 |
6 |
Six |
123 |
789 |
Y |
N |
Y |
| #7 |
7 |
Seven |
NULL |
NULL |
N |
Y |
Y |
| #8 |
8 |
Eight |
7 |
NULL |
Y |
N |
Y <=== Good Behavior |
| #9 |
9 |
Nine |
NULL |
11 |
Y |
N |
Y <=== Good Behavior |
| #10 |
10 |
Ten |
123 |
NULL |
Y |
N |
Y <=== Good Behavior |
| #11 |
11 |
Eleven |
NULL |
789 |
Y |
N |
Y <=== Good Behavior |
These are the correct and expected results. The new Check Constraint blocks the previous bad behavior. The NULL + NULL insertion into the composite FK columns is still allowed.
To confirm, if you retrieve the records from Table_D:
SELECT * FROM Table_D2;
You see the expected 3 rows:
| D_ID | D_Name | C_ID1 | C_ID2 |
|---|
| 1 | One | 7 | 11 |
| 2 | Two | 10 | 4 |
| 7 | Seven | NULL | NULL |
In this post you have seen both the good and bad behavior associated with SQL Server Foreign Keys, plus seen possible solutions to fix the bad behavior.
Please share any feedback, ideas, questions, or constructive criticism that you may have.