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

Материал из SQL эксперт
Версия от 13:25, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Adding a FOREIGN KEY to the Employees Table

   <source lang="sql">

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></source>


A statement that adds a foreign key constraint

   <source lang="sql">

ALTER TABLE YourTableName WITH CHECK ADD FOREIGN KEY (AccountNo) REFERENCES AnotherTableName(AccountNo)</source>


Cascading Updates and Deletes

   <source lang="sql">

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</source>


FOREIGN KEY Constraints

   <source lang="sql">

<column name> <nullability>

FOREIGN KEY REFERENCES (<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.</source>

ON DELETE and ON UPDATE Options

   <source lang="sql">

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</source>


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

   <source lang="sql">

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></source>


Referential Constraints

   <source lang="sql">

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</source>


Supporting Basic Referential Integrity with Foreign Keys

   <source lang="sql">

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</source>


The FOREIGN KEY Clause

   <source lang="sql">

[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></source>