Tuesday, April 5, 2011

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 

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

INSERT INTO dbo.Students 

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.


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

No comments:

Post a Comment