Foreign Key foreign key in SQL Server

This tutorial introduces how to use Foreign Key foreign key in SQL Server with syntax and examples.

What is the foreign key in SQL Server?

Foreign keys are used to increase references in SQL Server databases. A foreign key means that the value in this table must appear in another table.

The reference table is called the parent table, while the table containing the foreign key is called a child table. Foreign keys in the child table usually refer to the primary key PRIMARY KEY in the parent table.

Foreign keys can be created with the CREATE TABLE command or the ALTER TABLE command.

Create a foreign key using the CREATE TABLE command

Syntax

 CREATE TABLE bang_con ( cot1 kieudulieu [ NULL | NOT NULL ], cot2 kieudulieu [ NULL | NOT NULL ], … CONSTRAINT fk_ten FOREIGN KEY (cot_con1, cot_con2, … cot_con_n) REFERENCES bang_me (cot_me1, cot_me2, … cot_me_n) [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] );

bang_con

The name of the child table you want to create.

cot1, cot2

The column you want to create in the table. Each column has 1 data type, must be specified to contain NULL or NOT NULL values, otherwise default is NULL.

Data types in SQL Server

fk_ten

The name of the foreign key constraint to create.

cot_con1, cot_con2, . cot_con_n

The column in bang_con wants to refer to the primary key in bang_me.

bang_me

The name of the parent table contains the primary key used in bang_con.

cot_me1, cot_me2, . cot_me_n

The column that makes up the primary key in bang_me. Foreign keys will create constraints between data and columns cot_con1, cot_con2, . cot_con_n in bang_con.

ON DELETE

Option. Indicates what to do with the child data when the parent data is deleted. There are options NO ACTION, CASCADE, SET NULL and SET DEFAULT.

ON UPDATE

Option. Indicates what to do with the child data when the parent data is updated. There are options NO ACTION, CASCADE, SET NULL and SET DEFAULT.

NO ACTION

Use with ON DELETE or ON UPDATE, ie do nothing with child data when parent data is deleted or updated.

CASCADE

Use with ON DELETE or ON UPDATE, meaning that the child data is deleted or updated when the parent data is deleted or updated.

SET NULL

Use with ON DELETE or ON UPDATE, meaning that the child data is set to NULL when the parent data is deleted or updated.

SET DEFAULT

Use with ON DELETE or ON UPDATE, meaning that the child data is set to the default value when the parent data is deleted or updated.

Foreign Key (Cascade Delete) in SQL Server
Foreign Key (Set Null) foreign key in SQL Server

For example

 CREATE TABLE sanpham (id_sanpham INT PRIMARY KEY, ten_sanpham VARCHAR(50) NOT NULL, phan_loai VARCHAR(25) ); CREATE TABLE hangtonkho ( id_hangtonkho INT PRIMARY KEY, id_sanpham INT NOT NULL, soluong INT, luong_toithieu INT, luong_toida INT, CONSTRAINT fk_htk_id_sanpham FOREIGN KEY (id_sanpham) REFERENCES sanpham (id_sanpham) );

In the above example, we create the parent table, the sanpham table with the primary key consisting of the fields in id_sanpham. Next is the hangtonkho sub table.

The CREATE TABLE statement used to create the foreign key of the hangtonkho table is named fk_htk_id_sanpham. The foreign key forms the link between the id_sanpham column in the hangtonkho table and id_sanpham in the sanpham table.

The example above shows how to create a foreign key consisting of 1 column. Now create a foreign key with more than 1 information field.

For example

 CREATE TABLE sanpham ( ten_sanpham VARCHAR(50) NOT NULL, diadiem VARCHAR(50) NOT NULL, phanloai VARCHAR(25) CONSTRAINT sanpham_pk PRIMARY KEY (ten_sanpham, diadiem) );

 CREATE TABLE hangtonkho ( id_hangtonkho INT PRIMARY KEY, ten_sanpham VARCHAR(50) NOT NULL, diadiem VARCHAR(50) NOT NULL, soluong INT, luong_toithieu INT, luong_toida INT, CONSTRAINT fk_htk_sanpham FOREIGN KEY (ten_sanpham, diadiem) REFERENCES sanpham (ten_sanpham, diadiem) );

In this example, the sanpham parent table has a two-column primary key ten_sanpham and diadiem. Child tables and foreign keys must refer to these two columns.

Create a foreign key using the ALTER TABLE command

Syntax

 ALTER TABLE bang_con ADD CONSTRAINT fk_ten FOREIGN KEY (cot_con1, cot_con2, … cot_con_n) REFERENCES bang_me (cot_me1, cot_me2, … cot_me_n);

bang_con

The name of the child table you want to create.

fk_ten

The name of the foreign key constraint to create.

cot_con1, cot_con2, . cot_con_n

The column in bang_con wants to refer to the primary key in bang_me.

bang_me

The name of the parent table contains the primary key used in bang_con.

cot_me1, cot_me2, . cot_me_n

The column that makes up the primary key in bang_me. Foreign keys will create constraints between data and columns cot_con1, cot_con2, . cot_con_n in bang_con.

For example

 ALTER TABLE hangtonkho ADD CONSTRAINT fk_htk_id_sanpham FOREIGN KEY (id_sanpham) REFERENCES sanpham (id_sanpham);

This example creates the foreign key in the hangtonkho table called fk_htk_id_sanpham, referencing the sanpham table based on the id_sanpham column.

You can create foreign keys with more than 1 field as the example below.

 ALTER TABLE hangtonkho ADD CONSTRAINT fk_htk_sanpham FOREIGN KEY (ten_sanpham, diadiem) REFERENCES sanpham (ten_sanpham, diadiem);

The example above creates a foreign key named fk_htk_sanpham for the hangtonkho table, referring to the dashboard based on the ten_sanpham and diadiem columns.

Previous lesson: LOCAL TEMPORARY TABLE in SQL Server

The following article: Foreign Key Foreign Key (Cascade Delete) in SQL Server