SQL Server/T-SQL Tutorial/Constraints/Foreign Key

Adding a FOREIGN KEY to the Employees Table

6> CREATE TABLE Employees
7> (empid int NOT NULL primary key,
8>  mgrid int NULL,
9>  empname varchar(25) NOT NULL,
10>  salary money NOT NULL);
11> GO
1>
2> INSERT INTO employees(empid, mgrid, empname, salary) VALUES( 1, NULL, "Nancy",  $10000.00)
3> INSERT INTO employees(empid, mgrid, empname, salary) VALUES( 2,    1, "Andrew",  $5000.00)
4> INSERT INTO employees(empid, mgrid, empname, salary) VALUES( 3,    1, "Janet",   $5000.00)
5> INSERT INTO employees(empid, mgrid, empname, salary) VALUES( 4,    1, "Margaret",$5000.00)
6> INSERT INTO employees(empid, mgrid, empname, salary) VALUES( 5,    2, "Steven",  $2500.00)
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> ALTER TABLE Employees ADD CONSTRAINT FK_Employees_Employees
4>   FOREIGN KEY(mgrid)
5>   REFERENCES Employees(empid);
6> GO
1>
2> drop table Employees;
3> GO
1>
2>
3>

A statement that adds a foreign key constraint

ALTER TABLE YourTableName WITH CHECK
ADD FOREIGN KEY (AccountNo) REFERENCES AnotherTableName(AccountNo)

Cascading Updates and Deletes

6> CREATE TABLE Orders (
7>      OrderID int primary key ,
8>      CustomerID nchar (5) NULL ,
9>      EmployeeID int NULL ,
10>     OrderDate datetime NULL ,
11>     RequiredDate datetime NULL ,
12>     ShippedDate datetime NULL ,
13>     ShipVia int NULL ,
14>     Freight money NULL DEFAULT (0),
15>     ShipName nvarchar (40) NULL ,
16>     ShipAddress nvarchar (60) NULL ,
17>     ShipCity nvarchar (15) NULL ,
18>     ShipRegion nvarchar (15) NULL ,
19>     ShipPostalCode nvarchar (10) NULL ,
20>     ShipCountry nvarchar (15) NULL
21> )
22> GO
1>
2>
3>
4>    CREATE TABLE OrderDetails
5>    (
6>       OrderID      int           NOT NULL,
7>       PartNo       varchar(10)   NOT NULL,
8>       Description  varchar(25)   NOT NULL,
9>       UnitPrice    money         NOT NULL,
10>       Qty          int           NOT NULL,
11>       CONSTRAINT    PKOrderDetails
12>          PRIMARY KEY   (OrderID, PartNo),
13>       CONSTRAINT    FKOrderContainsDetails
14>          FOREIGN KEY   (OrderID)
15>             REFERENCES Orders(OrderID)
16>             ON UPDATE  NO ACTION
17>             ON DELETE  CASCADE
18>    )
19> GO
1>
2> drop table OrderDetails;
3> drop table Orders;
4> GO

FOREIGN KEY Constraints

<column name> <data type> <nullability>
FOREIGN KEY REFERENCES <table name>(<column name>)
    [ON DELETE {CASCADE|NO ACTION}]
    [ON UPDATE {CASCADE|NO ACTION}]
11> CREATE TABLE Customers (
12>     CustomerID nchar (5) NOT NULL PRIMARY KEY ,
13>     CompanyName nvarchar (40) NOT NULL ,
14>     ContactName nvarchar (30) NULL ,
15>     ContactTitle nvarchar (30) NULL ,
16>     Address nvarchar (60) NULL ,
17>     City nvarchar (15) NULL ,
18>     Region nvarchar (15) NULL ,
19>     PostalCode nvarchar (10) NULL ,
20>     Country nvarchar (15) NULL ,
21>     Phone nvarchar (24) NULL ,
22>     Fax nvarchar (24) NULL
23> )
24> GO
1>
2>
3>
4>    CREATE TABLE Orders
5>    (
6>       OrderID      int   IDENTITY   NOT NULL
7>          PRIMARY KEY,
8>       CustomerNo   nchar              NOT NULL
9>          FOREIGN KEY REFERENCES Customers(CustomerID),
10>       OrderDate    smalldatetime    NOT NULL,
11>       EmployeeID   int              NOT NULL
12>    )
13>    GO
Msg 1753, Level 16, State 1, Server J\SQLEXPRESS, Line 4
Column "Customers.CustomerID" is not the same length as referencing column "Orders.CustomerNo" in foreign key "FK__Orders__Customer__43C1CFF5". Columns participating in a foreign key relationship must
 be defined with the same length.
Msg 1750, Level 16, State 1, Server J\SQLEXPRESS, Line 4
Could not create constraint. See previous errors.

ON DELETE and ON UPDATE Options

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

Re-creating the FOREIGN KEY with NO ACTION (Implicitly)

6> CREATE TABLE Employees
7> (empid int NOT NULL primary key,
8>  mgrid int NULL,
9>  empname varchar(25) NOT NULL,
10>  salary money NOT NULL);
11> GO
1>
2> INSERT INTO employees(empid, mgrid, empname, salary) VALUES( 1, NULL, "Nancy",  $10000.00)
3> INSERT INTO employees(empid, mgrid, empname, salary) VALUES( 2,    1, "Andrew",  $5000.00)
4> INSERT INTO employees(empid, mgrid, empname, salary) VALUES( 3,    1, "Janet",   $5000.00)
5> INSERT INTO employees(empid, mgrid, empname, salary) VALUES( 4,    1, "Margaret",$5000.00)
6> INSERT INTO employees(empid, mgrid, empname, salary) VALUES( 5,    2, "Steven",  $2500.00)
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> ALTER TABLE Employees ADD CONSTRAINT FK_Employees_Employees
4>   FOREIGN KEY(mgrid)
5>   REFERENCES Employees(empid)
6> GO
1>
2>
3> ALTER TABLE Employees DROP CONSTRAINT FK_Employees_Employees
4> GO
1> drop table Employees;
2> GO
1>

Referential Constraints

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

Supporting Basic Referential Integrity with Foreign Keys

4>
5> CREATE TABLE Classes(
6>     ClassID int PRIMARY KEY,
7>     ClassTitle varchar(50)
8> )
9> GO
1>
2> CREATE TABLE ClassGrades(
3>     ClassID int REFERENCES Classes(ClassID)
4>         ON UPDATE CASCADE,
5>     StudentID int,
6>     GradeLetter varchar(2),
7>     Constraint PK_ClassGrades
8>         PRIMARY KEY(ClassID, StudentID)
9> )
10>
11> INSERT Classes VALUES(1,"SQL")
12> INSERT Classes VALUES(999,"Java")
13> GO
(1 rows affected)
(1 rows affected)
1>
2> INSERT ClassGrades VALUES(1, 1, "C+")
3>
4> INSERT ClassGrades VALUES(1, 2, "A+")
5> INSERT ClassGrades VALUES(999, 2, "A")
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> INSERT ClassGrades VALUES(998, 1, "B")
3> GO
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__ClassGrad__Class__4F7D9B64". The conflict occurred in database "master", table "dbo.Classes", column "ClassID".
The statement has been terminated.
1>
2> drop table ClassGrades;
3> GO
1>
2> drop table Classes;
3> GO

The FOREIGN KEY Clause

[CONSTRAINT c_name]
      [[FOREIGN KEY] (col_name1 [{, col_name2} ...]])
      REFERENCES table_name (col_name3 [{, col_name4} ...])
        [ON DELETE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}]
         [ON UPDATE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}]
14>
15> CREATE TABLE employee (emp_no INTEGER NOT NULL,
16>               emp_fname CHAR(20) NOT NULL,
17>               emp_lname CHAR(20) NOT NULL,
18>               dept_no CHAR(4) NULL,
19>       CONSTRAINT prim_empl PRIMARY KEY (emp_no))
20> 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_works PRIMARY KEY (emp_no, project_no),
8>     CONSTRAINT foreign_works FOREIGN KEY (emp_no) REFERENCES employee (emp_no))
9> GO
1>
2>
3> drop table myProject;
4> GO
1>
2> drop table employee;
3> GO
1>