Cascading Updates and Deletes : Foreign Key « Constraints « SQL Server / T-SQL Tutorial






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








7.3.Foreign Key
7.3.1.The FOREIGN KEY Clause
7.3.2.FOREIGN KEY Constraints
7.3.3.A statement that adds a foreign key constraint
7.3.4.ON DELETE and ON UPDATE Options
7.3.5.Adding a FOREIGN KEY to the Employees Table
7.3.6.Re-creating the FOREIGN KEY with NO ACTION (Implicitly)
7.3.7.Referential Constraints
7.3.8.Cascading Updates and Deletes
7.3.9.Supporting Basic Referential Integrity with Foreign Keys