SQL Server/T-SQL/Constraints/CASCADE

DELETE CASCADE and UPDATE CASCADE

   <source lang="sql">

1> 2> CREATE TABLE employee (emp_no INTEGER NOT NULL CONSTRAINT prim_empl PRIMARY KEY, 3> emp_fname CHAR(20) NOT NULL, 4> emp_lname CHAR(20) NOT NULL, 5> dept_no CHAR(4) NULL) 6> 7> CREATE TABLE project (project_no CHAR(4) NOT NULL CONSTRAINT prim_pro PRIMARY KEY, 8> project_name CHAR(15) NOT NULL, 9> budget FLOAT NULL) 10> 11> CREATE TABLE works_on1 12> (emp_no INTEGER NOT NULL, 13> project_no CHAR(4) NOT NULL, 14> job CHAR (15) NULL, 15> enter_date DATETIME NULL, 16> CONSTRAINT prim_works1 PRIMARY KEY(emp_no, project_no), 17> CONSTRAINT foreign1_works1 FOREIGN KEY(emp_no) REFERENCES employee(emp_no) ON DELETE CASCADE, 18> CONSTRAINT foreign2_works1 FOREIGN KEY(project_no) REFERENCES project(project_no) ON UPDATE CASCADE) 19> 20> -- The creation of the works_on1 table that uses the ON DELETE CASCADE and ON UPDATE CASCADE options. 21> drop table works_on1 22> GO 1> 2> drop table project 3> drop table employee 4> GO 1>

      </source>