You are the designer of a SQL database on an instance of SQL Server 2008. Your database contains a Course table and an Instructor table defined as follows:
You want to create a FOREIGN KEY constraint to associate the two tables and prevent a user from deleting an instructor who is currently assigned to teach a course.
Which clause should you include in your FOREIGN KEY constraint?
A. ON DELETE NO ACTION
B. ON DELETE CASCADE
C. ON DELETE SET NULL
D. ON DELETE SET DEFAULT
Correct Answer: A
Explanation/Reference:
In this scenario, you might use the following statement to create the necessary FOREIGN KEY constraint:
ALTER TABLE Course ADD CONSTRAINT FK_Instructor FOREIGN KEY (InstructorID)REFERENCES Instructor (InstructorID)ON DELETE NO ACTION;
This statement includes the ADD CONSTRAINT clause of the ALTER TABLE statement to add a FOREIGN KEY constraint to the Course table. The REFERENCES clause specifies the referenced column in the parent table. When you include the ON DELETE NO ACTION clause, it ensures that referential integrity is maintained.
If a referenced row in the parent table is deleted, then the delete fails if child rows exist. With the given statement, a user could not delete an instructor who was assigned to courses in the Course table. You should note that NO ACTION is the default for ON DELETE and ON UPDATE.
You should not include the ON DELETE CASCADE clause in your FOREIGN KEY constraint. If you did so and a user deleted an instructor that was assigned to teach a course, the delete operation would be performed, and all courses for the instructor would be deleted as well.
You should not include the ON DELETE SET NULL clause in your FOREIGN KEY constraint. If you did so and a user deleted an instructor who was assigned to teach a course, the InstructorID column would be set to NULL for all of the instructor’s courses in the Course table. You should note that if the foreign key consists of multiple columns, all of the columns in the foreign key would be set to NULL.
You should not include the ON DELETE SET DEFAULT clause in your FOREIGN KEY constraint. If you did so and a user deleted an instructor who was assigned to teach a course, the InstructorID column would be set to the default value for all of that instructor’s courses in the Course table. You should note that if the foreign key consists of multiple columns, all of the columns in the foreign key are set to the default value. In addition, you should ensure that each foreign key column has a DEFAULT definition; otherwise, the column will be assigned a NULL value.
