SQL Server/T-SQL Tutorial/Constraints/Foreign Key
Содержание
- 1 Adding a FOREIGN KEY to the Employees Table
- 2 A statement that adds a foreign key constraint
- 3 Cascading Updates and Deletes
- 4 FOREIGN KEY Constraints
- 5 ON DELETE and ON UPDATE Options
- 6 Re-creating the FOREIGN KEY with NO ACTION (Implicitly)
- 7 Referential Constraints
- 8 Supporting Basic Referential Integrity with Foreign Keys
- 9 The FOREIGN KEY Clause
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>