ON DELETE and ON UPDATE Options : Foreign Key « Constraints « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Constraints
3. Foreign Key

The use of the ON DELETE and ON UPDATE options.

7>
8>
9> CREATE TABLE department(
10>    dept_no CHAR(4) NOT NULL,
11>    dept_name CHAR(25) NOT NULL,
12>    location CHAR(30) NULL,
13>    CONSTRAINT prim_dept PRIMARY KEY (dept_no))
14> GO
1>
2> CREATE TABLE employee (
3>            emp_no INTEGER NOT NULL,
4>            emp_fname CHAR(20) NOT NULL,
5>            emp_lname CHAR(20) NOT NULL,
6>            dept_no CHAR(4) NULL,
7>            CONSTRAINT prim_emp PRIMARY KEY (emp_no),
8>            CONSTRAINT foreign_emp FOREIGN KEY(dept_no) REFERENCES
9>       department(dept_no))
10> GO
1>
2> CREATE TABLE project (project_no CHAR(4) NOT NULL,
3>            project_name CHAR(15) NOT NULL,
4>            budget FLOAT NULL,
5>            CONSTRAINT prim_proj PRIMARY KEY (project_no))
6> GO
1>
2> CREATE TABLE myProject(
3>       emp_no INTEGER NOT NULL,
4>       project_no CHAR(4) NOT NULL,
5>       job CHAR (15) NULL,
6>       enter_date DATETIME NULL,
7>       CONSTRAINT prim_works1 PRIMARY KEY(emp_no, project_no),
8>       CONSTRAINT foreign1_works1 FOREIGN KEY(emp_no)      REFERENCES employee(emp_no) ON DELETE CASCADE,
9>       CONSTRAINT foreign2_works1 FOREIGN KEY(project_no)  REFERENCES project(project_no) ON UPDATE CASCADE)
10>
11>
12> GO
1>
2> drop table myProject;
3> GO
1>
2> drop table project;
3> GO
1> drop table employee;
2> GO
1> drop table department;
2> GO