Mega Code Archive
How to use foreign key constraints
<![CDATA[
The syntax of a column-level foreign key constraint
[FOREIGN KEY] REFERENCES ref_table_name (ref_column_name)
[ON DELETE {CASCADE|NO ACTION}]
[ON UPDATE {CASCADE|NO ACTION}]
The syntax of a table-level foreign key constraint
FOREIGN KEY (column_name_1 [, column_name_2]...)
REFERENCES ref_table_name (ref_column_name_1
[, ref_column_name_2]...)
[ON DELETE {CASCADE|NO ACTION}]
[ON UPDATE {CASCADE|NO ACTION}]
A foreign key constraint defined at the column level
A statement that creates the primary key table
28> CREATE TABLE Bankers
29> (BankerID INT NOT NULL PRIMARY KEY,
30> BankerName VARCHAR(50) NOT NULL)
31> GO
1>
2> --A statement that creates the foreign key table
3>
4> CREATE TABLE Billings
5> (BillingID INT NOT NULL PRIMARY KEY,
6> BankerID INT NOT NULL REFERENCES Bankers (BankerID),
7> BillingTotal MONEY NULL)
8> GO
1>
2> --An INSERT statement that fails because a related row doesn't exist
3>
4> INSERT Billings VALUES (1, 99, 100)
5> GO
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Billings__Banker__5F9EF494". The conflict occurred in database "master", table "dbo.Bankers", column 'BankerID'.
The statement has been terminated.
1>
2>
3> drop table Billings;
4> drop table Bankers;
5> GO
]]>