Question of the Day

SQLServerCentral.com published my Question of the Day submission today! It's been pretty-well received so far.

I feel like I'm contributing something.

4/8/2011 Update:

It's probably OK to post the question now, so here it is.

Note: multiple commenters pointed out that option C should be phrased as 'The last INSERT statement will fail.'

Foreign Keys

What will the result of this code be?

USE AdventureWorks2008 
GO 

CREATE TABLE dbo.Students (
 StudentID INT UNIQUE, 
 LastName VARCHAR(20), 
 FirstName VARCHAR(20) 
)

INSERT INTO dbo.Students 
 VALUES(1,'Washington','George'),
       (2,'Adams','John'),
       (3,'Jefferson','Thomas')

CREATE TABLE dbo.ClassList (
 ClassID VARCHAR(6),
 StudentID INT REFERENCES dbo.Students(StudentID)
) 

INSERT INTO dbo.ClassList VALUES('HIS101',4)

A. The ClassList table will not be created.
B. The code will complete successfully.
C. The INSERT statement will fail.

Correct Answer: 

C. The INSERT statement will fail.

Explanation:

You may think that a referenced key must be a primary key, but according to Books Online, a foreign key is linked to a primary key or unique key. Therefore, the ClassList table has a valid foreign key constraint, which prevents the record from being inserted.

Reference: Creating and Modifying FOREIGN KEY Constraints - http://msdn.microsoft.com/en-us/library/ms177463.aspx