A foreign key will also prevent the referenced (master) table from being dropped. Consider this code snippet:
IF EXISTS (SELECT object_id(N'dbo.mst'))DROP TABLE dbo.mstCREATE TABLE dbo.mst (id INT IDENTITY(1,1) PRIMARY KEY,txt varchar(10))INSERT dbo.mst (txt) VALUES ('foo'),('bar')CREATE TABLE dbo.child (id INT IDENTITY(1,1) PRIMARY KEY,name varchar(100),mstid INT)ALTER TABLE [dbo].child WITH CHECK ADD CONSTRAINT [FK_CHILD_SINGLE] FOREIGN KEY(mstid)REFERENCES [dbo].mst ([id])
This code works fine once, as dbo.mst does not exist, so the drop table is not run. However once the check constraint is in place, the run will fail:
Msg 3726, Level 16, State 1, Line 2Could not drop object 'dbo.mst' because it is referenced by a FOREIGN KEY constraint.Msg 2714, Level 16, State 6, Line 3There is already an object named 'mst' in the database.
Foreign key behaviour
Having created the child table, let’s put some data in it:
INSERT dbo.child( name, mstid )VALUES( 'Barney', 1 )(1 row(s) affected)
Great! We can verify that the data has been inserted by selecting records from the child table.
Now, what if we try to insert a record where the mstid doesn’t exist in the mst table?INSERT dbo.child( name, mstid )VALUES( 'Fred', 0 )
We would not expect this to work, and sure enough:
Now, lets say we have the situation where we would like to have a foreign key on a non-mandatory value.
Msg 547, Level 16, State 0, Line 1The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CHILD_SINGLE". The conflict occurred in database "Test_DB", table "dbo.mst", column 'id'.The statement has been terminated.
INSERT dbo.child( name, mstid )VALUES( 'Wilma', null )(1 row(s) affected)
So if the field is null, then it isn’t considered to break the foreign key.
Thus, a Foreign Key can be summarised as a check on whether a value that exists is actually part of the referenced set.
What about composite foreign key behaviour?
A quick search of Books Online produces the following (from http://msdn.microsoft.com/en-us/library/ms175464.aspx) :
“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.”
So, that’s an interesting nugget. Let’s test this. A different table structure is called for:
IF EXISTS (SELECT object_id(N'dbo.child'))DROP TABLE dbo.childIF EXISTS (SELECT object_id(N'dbo.mst'))DROP TABLE dbo.mstCREATE TABLE dbo.mst (id INT,idtwo INT,txt varchar(10),Primary key (id, idtwo))CREATE TABLE dbo.child (id INT IDENTITY(1,1) PRIMARY KEY,name varchar(100),mstid INT,mstidtwo INT)ALTER TABLE [dbo].child WITH CHECK ADD CONSTRAINT [FK_CHILD_DOUBLE] FOREIGN KEY(mstid,mstidtwo)REFERENCES [dbo].mst ([id],idtwo)
And some data:
INSERT dbo.mst (id,idtwo,txt) VALUES (1,1,'foo'),(1,2,'bar')INSERT dbo.child( name, mstid, mstidtwo )VALUES( 'test both valid', 1, 1 )INSERT dbo.child( name, mstid, mstidtwo )VALUES( 'test not valid', 1, 3 )INSERT dbo.child( name, mstid, mstidtwo )VALUES( 'test one valid other null', 1, null )INSERT dbo.child( name, mstid, mstidtwo )VALUES( 'test both null', null, null )
INSERT dbo.child( name, mstid, mstidtwo )VALUES( 'test one valid other null 2', null, 2 )
So, what results?
SELECT * FROM dbo.mst
id idtwo txt 1 1 foo 1 2 bar
SELECT * FROM dbo.child
id name mstid mstidtwo 1 test both valid 1 1 3 test one valid other null 1 NULL 4 test both null NULL NULL 5 test one valid other null 2 NULL 2
That’s a little odd.. The only record that failed was the one that had known wrong (as opposed to null) values.
This is the natural extension of the single-field version, but still a bit of a surprise, and one that could trip you up if you didn’t expect it.What about if we follow the advice from Books Online, and make the columns non-nullable?
Well, the first thing to note, is that it isn’t the columns in mst that must be non-nullable, it’s the columns in the child table. This is key (if you want to prove it to yourself, try altering the above code to match!)
So, if we change the child table to be
CREATE TABLE dbo.child (id INT IDENTITY(1,1) PRIMARY KEY,name varchar(100),mstid INT NOT NULL,mstidtwo INT NOT NULL)
And then re-run the insert statements from above, what do we get?
As might be predicted, the output looks like :
id name mstid mstidtwo 1 test both valid 1 1
However, let's look at the messages returned:
(2 row(s) affected)(1 row(s) affected)Msg 547, Level 16, State 0, Line 11The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CHILD_DOUBLE". The conflict occurred in database "Test_DB", table "dbo.mst".The statement has been terminated.Msg 515, Level 16, State 2, Line 19Cannot insert the value NULL into column 'mstidtwo', table 'Test_DB.dbo.child'; column does not allow nulls. INSERT fails.The statement has been terminated.Msg 515, Level 16, State 2, Line 27Cannot insert the value NULL into column 'mstid', table 'Test_DB.dbo.child'; column does not allow nulls. INSERT fails.The statement has been terminated.Msg 515, Level 16, State 2, Line 34Cannot insert the value NULL into column 'mstid', table 'Test_DB.dbo.child'; column does not allow nulls. INSERT fails.The statement has been terminated.(2 row(s) affected)(1 row(s) affected)
We note that the insert wasn’t refused by the Foreign Key if there were nulls in the table – the ‘nullability’ of the column takes precident over the Foreign Key. This makes sense as it’s quicker to validate when only looking at one table, but still a point of note.
This means our conclusion of “a Foreign Key can be summarised as a check on whether a value that exists is actually part of the referenced set” is still true, but it’s worth being aware of where the model can break down if you allow null values in the set.
