![]() If no default value is specified for dept_id in the employee table, then the above deletion will set the value as NULL. Note that there is default value 3 specified in the employee table for the dept_id column. Let's check the data in the employee table. We defined foreign key constraint with ON DELETE SET DEFAULT, so referencing row with emp_id = 1 in employee table whose dept_id was 1 is now set to DEFAULT value which is 3. The following example demonstrates the NO ACTION referential action.ĭELETE FROM department WHERE dept_id = 1 Īs you can see, it allowed deletion of the department. The NO ACTION produces an error indicating that the deletion or update would create a foreign key constraint violation. The NO ACTION referential action is the default action if ON DELETE or ON UPDATE clause is not specified. NO ACTION – Raise an Error on Delete or Update Note: The foreign key column name does not need to be the same as a primary key column, but it's advisable to do so for readability purposes. ![]() So, it will consider the default NO ACTION. Notice that we did not define any action such as ON DELETE or ON UPDATE clause. ![]() The above foreign key established a one-to-many relation between department and employee table where a department can have zero or more employees, and one employee cannot have more than one department. The CONSTRAINT FK_employee_department specifies the foreign key name FK_employee_department, FOREIGN KEY(dept_id) specifies the foreign key column in the employee table,Īnd REFERENCES department(dept_id) specifies that the foreign key column refers to the dept_id column of the department table. In the above example, the dept_id column in the employee table is defined as a foreign key column that references the primary key column dept_id of the department table.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |