You are a database developer of a Microsoft SQL Server 2012 database.
The database contains a table named Customers that has the following definition:
You need to ensure that the CustomerId column in the Orders table contains only values that
exist in the CustomerId column of the Customer table.
Which Transact-SQL statement should you use?
A.
ALTER TABLE Orders
ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES
Customer (CustomerId)
B.
ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID)
REFERENCES
Orders (CustomerId)
C.
ALTER TABLE Orders
ADD CONSTRAINT CK_Crders_CustomerID
CHECK (CustomerId IN (SELECT CustomerId FROM Customer))
D.
ALTER TABLE Customer
ADD OrderId INT NOT NULL;
ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES
Orders
(CrderlD);
E.
ALTER TABLE Orders
ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms189049.aspx
B & D alter Customer table and E creates Primary Key, which are clearly wrong.
C uses CHECK incorrectly. CHECK only accepts scalar expressions, not queries.