You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01 B01. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. The tables in the CK_Sales database are shown in the following database diagram.
Domain.com decides to switch to commission-based salaries for its Sales staff. The manager of the Sales department will run queries against the Invoices table to calculate the commission for each sales person. To ensure the success of the queries, you want to enforce the values that the SalesPersonID column in the Invoices table will accept.
What should you do?
A.
Create a foreign key constraint that references the CK_Sales.SalesPersons table.
B.
Create a check key constraint that references the CK_Sales.SalesPersons table.
C.
Create a DML trigger that references the CK_Sales.SalesPersons table.
D.
Create a unique constraint on the SalesPersonID column of the CK_Sales.Invoices table.
Explanation:
A foreign key constraint that references the CK_Sales.SalesPersons table will ensure that only SalesPersonIDs that exist in the SalesPersons table may be used in the Invoices table.
Incorrect Answers:
B: A check constraint defines the value that can be entered into a column.
B: A DML trigger fires when a UPDATE, INSERT or DELETE statement is run against a table. This is not what is required.
D: A unique constraint ensures that a value in a column only appears once in that column. This is not what is required.
Reference:
Microsoft SQL Server 2005 Books Online (2007), Index: constraints [SQL Server] Microsoft SQL Server 2005 Books Online (2007), Index: constraints [SQL Server], vs.
DML triggers
Microsoft SQL Server 2005 Books Online (2007), Index: DML triggers