Referential Constraints : Foreign Key « Constraints « SQL Server / T-SQL Tutorial

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

4>
5> CREATE TABLE department(
6>    dept_no CHAR(4) NOT NULL,
7>    dept_name CHAR(25) NOT NULL,
8>    location CHAR(30) NULL,
9>    CONSTRAINT prim_dept PRIMARY KEY (dept_no))
10> 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 (emp_no INTEGER NOT NULL,
3>        project_no CHAR(4) NOT NULL,
4>        job CHAR (15) NULL,
5>        enter_date DATETIME NULL,
6>        CONSTRAINT prim_works PRIMARY KEY(emp_no, project_no),
7>        CONSTRAINT foreign1_works FOREIGN KEY(emp_no) REFERENCES
8>        employee(emp_no),
9>        CONSTRAINT foreign2_works FOREIGN KEY(project_no) REFERENCES
10>        project(project_no))
11> 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