How to use foreign key constraints : Foreign Key « Constraints « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Constraints
3. Foreign Key

How to use foreign key constraints

 


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

 

Related examples in the same category