Enforcing Referential Activity in a New Table

Enforce referential integrity in a new table.

1. Create the primary table, for example:

   CREATE TABLE DEPT(DeptNo int primary key,
   DeptName varchar(20),
   Mgr int );

2. Create the foreign table. For example, in this statement, the default role name for the specified foreign key is DEPT:

   CREATE TABLE EMPLOYEE(EmpNo int primary key,
   DeptNo int references DEPT(DeptNo)
   on delete restrict,
   LastName varchar(20),
   FirstName varchar(20),
   Salary int);

3. Add the foreign key constraint. For example:

   CREATE TABLE EMPLOYEE(EmpNo int primary key,
   DeptNo int,
   LastName varchar(20),
   FirstName varchar(20),
   Salary int,
   FOREIGN KEY EMP_DEPT(DeptNo) REFERENCES DEPT(DeptNo));

CREATE TABLE EMPLOYEE(EmpNo int primary key,
DeptNo int,
LastName varchar(20),
FirstName varchar(20),
Salary int,
FOREIGN KEY EMP_DEPT(DeptNo) REFERENCES DEPT(DeptNo));