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>

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

Содержание

Adding a FOREIGN KEY to the Employees Table

A statement that adds a foreign key constraint

Cascading Updates and Deletes

FOREIGN KEY Constraints

ON DELETE and ON UPDATE Options

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

Referential Constraints

Supporting Basic Referential Integrity with Foreign Keys

The FOREIGN KEY Clause

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты