You are a database developer on an instance of SQL Server 2008. You have an Employee table created with the following statement:
CREATE TABLE Employee (
EmpID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(35) NOT NULL,
FirstName varchar(25) NOT NULL,
HireDate datetime,
Salary money,
MgrID int);
The MgrID column will contain the EmpID associated with each employee’s manager. You want to create a self-referencing relationship to support this.
Which action should you take?
A. Create a DML trigger.
B. Create a FOREIGN KEY constraint on the EmpID column.
C. Create a FOREIGN KEY constraint on the MgrID column.
D. Create a CHECK constraint on the MgrID column.
Correct Answer: C
Explanation/Reference:
A FOREIGN KEY constraint is used to establish a relationship between two tables or to establish a self-referencing relationship within a single table.
You can create a FOREIGN KEY CONSTRAINT using the ALTER TABLE statement:
ALTER TABLE Employee
ADD FOREIGN KEY (MgrID) REFERENCES Employee (EmpID)
A CHECK constraint consists of a Boolean expression that evaluates to either TRUE or FALSE. If the expression evaluates to TRUE, the value is allowed for the column, and if the expression evaluates to FALSE, the value is not allowed for the column. CHECK constraints can be defined at the table level or column level, but only CHECK constraints defined at the table level can use columns other than the constrained column in the constraint expression.
DML triggers contain Transact-SQL code that executes when a DML operation, such as an INSERT, UPDATE, MERGE, or DELETE, is performed. INSTEAD OF triggers fire in place of the triggering operation, and AFTER triggers fire after the triggering operation completes successfully. You might use a DML trigger to prevent the original triggering operation or to perform additional or alternative actions.