Mega Code Archive
ON DELETE and ON UPDATE Options
<![CDATA[
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
]]>