Foreign keys in SQL Server is a concept that any programmer needs to know. In this article, let's find out with Quantrimang.com Things to know about SQL Server foreign keys okay!
Foreign keys allow database administrators to easily identify the different connections that exist within a database management system.
SQL performs data calculations in a database management system. These databases contain different tables. They save data on a specific object. For example, if you have a database about car rentals, one object (or table) in the database will be customers. Databases contain table links, each row contains a record and each column contains certain attribute data.
In a database management system, each record (or row) must be unique.
Primary keys
Although the rule is that each record in a table must be unique, this is not always the case. Continuing with the car rental database example, if the database contains two customers named “John Brown,” then John Brown would probably return a Mercedes-Benz that he did not rent .
Creating primary keys minimizes this risk. In a SQL database management system, a primary key is a unique identifier that distinguishes one record from another.
Therefore, each record in the SQL database management system needs a primary key.
Use primary key in database
To include primary keys in a SQL database management system, you can simply add it as a normal attribute when creating a new table. Basically, the customer table will contain 4 attributes (or columns):
- Vehicle owner ID (will contain master key)
- Name
- Last name
- Phone number
Sample code:
/* tạo bảng ghi mới trong bảng khách hàng */
INSERT INTO Customers VALUES
('0004',
'John',
'Brown',
'111-999-5555');
Why use foreign keys in SQL?
To normalize the data
Foreign keys help programmers normalize data in multiple tables and reduce redundancy. That means a database can have many related tables.
Avoid incorrect data when inserting
If two database tables are related through a field (attribute), using a foreign key ensures that incorrect data is not inserted into that field. This helps eliminate errors at the database level.
What is a foreign key in SQL Server?
Foreign keys are used to increase referentiality in SQL Server databases. A foreign key means that values in one table must appear in another table.
The reference table is called the parent table, and the table containing the foreign key is called the child table. Foreign keys in child tables often refer to the primary key PRIMARY KEY in the parent table.
Foreign keys can be created using the CREATE TABLE command or the ALTER TABLE command.
Create foreign keys using 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
Name of the child table you want to create.
cot1, cot2
Column you want to create in the table. Each column has 1 data type, which must be specified as containing the value NULL or NOT NULL, otherwise it will default to NULL.
Data types in SQL Server
fk_name
Name of the foreign key constraint you want to create.
cot_con1, cot_con2, … cot_con_n
The column in bang_child wants to refer to the primary key in bang_me.
bang_me
The name of the parent table containing the primary key used in state_child.
cot_me1, cot_me2, … cot_me_n
The column that makes up the primary key in bang_me. The foreign key will create a constraint between the data and the columns cot_con1, cot_con2, … cot_con_n in state_con.
ON DELETE
Options. Indicates what will be done with child data when the parent data is deleted. There are NO ACTION, CASCADE, SET NULL and SET DEFAULT options.
ON UPDATE
Options. Indicates what will be done with the child data when the parent data is updated. There are NO ACTION, CASCADE, SET NULL and SET DEFAULT options.
NO ACTION
Used with ON DELETE or ON UPDATE, meaning do nothing with child data when parent data is deleted or updated.
CASCADE
Used with ON DELETE or ON UPDATE, it means that the child data is deleted or updated when the parent data is deleted or updated.
SET NULL
Used with ON DELETE or ON UPDATE, meaning the child data is set to NULL when the parent data is deleted or updated.
SET DEFAULT
Used with ON DELETE or ON UPDATE, it means that the child data is set to the default value when the parent data is deleted or updated.
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 as the sanpham table with a primary key consisting of fields in id_sanpham. Next is to create the hangtonkho child table.
The CREATE TABLE command is used to create a foreign key of the hangtonkho table named fk_htk_id_sanpham. The foreign key forms a link between the id_sanpham column in the hangtonkho table and the id_sanpham column in the sanpham table.
The example above shows how to create a 1-column foreign key. Now let's 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 parent table sanpham has a primary key consisting of 2 columns: name_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
Name of the child table you want to create.
fk_name
Name of the foreign key constraint you want to create.
cot_con1, cot_con2, … cot_con_n
The column in bang_child wants to refer to the primary key in bang_me.
bang_me
The name of the parent table containing the primary key used in state_child.
cot_me1, cot_me2, … cot_me_n
The column that makes up the primary key in bang_me. The foreign key will create a constraint between the data and the columns cot_con1, cot_con2, … cot_con_n in state_con.
For example
ALTER TABLE hangtonkho
ADD CONSTRAINT fk_htk_id_sanpham
FOREIGN KEY (id_sanpham)
REFERENCES sanpham (id_sanpham);
This example creates a foreign key in the hangtonkho table called fk_htk_id_sanpham, which references the sanpham table based on the id_sanpham column.
It is possible to create foreign keys with more than 1 field like the example below.
ALTER TABLE hangtonkho
ADD CONSTRAINT fk_htk_sanpham
FOREIGN KEY (ten_sanpham, diadiem)
REFERENCES sanpham (ten_sanpham, diadiem);
The above example creates a foreign key named fk_htk_sanpham for the hangtonkho table, referencing the sanpham table based on the ten_sanpham and diadiem columns.
Restrictions and limitations when creating foreign key relationships
- A foreign key constraint does not have to be linked only to a primary key constraint in another table. Foreign keys can also be defined to reference columns of a UNIQUE constraint in another table.
- When a non-NULL value is entered into a foreign key constraint column, the value must exist in the referenced column. Otherwise, a foreign key violation error message is returned. To ensure that all values of the member foreign key constraints have been verified, specify NOT NULL on all participating columns.
- Foreign key constraints can only reference tables in the same database on the same server.
- Foreign key constraints can refer to other columns in the same table, and are referenced as self-references.
- Column-level foreign key constraints can list only one reference column. This column must have the same data type on the constraint column defined.
- A foreign key constraint that has been specified at the table level must have the same number of reference columns as the column number is in the constraint column list. The data type of each reference must also be the same as the corresponding column in the column list.
Previous article: LOCAL TEMPORARY TABLE in SQL Server
Next article: Foreign Key Foreign Key Foreign Key (Cascade Delete) in SQL Server