What is a foreign key in SQL Server

In this article, we will seek an answer to an important question – “What is a foreign key in SQL Server?”. At the same time, we will give some seconder answers to this question. In this way, we can understand the foreign key concept more clearly.

Introduction

A foreign key is a column or set of columns that allow us to establish a referential backlink between the data in two tables. This referential backlink helps to match the foreign key column data with the data of the referenced table data. The referenced table is called the parent table and the table that involves a foreign key is called the child table. In addition, if a foreign key references another column of the same table, this reference type is called a self-reference.

Until this part of the article, we have answered the “What is a foreign key in SQL” question briefly. Now, we will take some examples in order to understand the foreign key designing model and usage details.

Create a foreign key

Suppose that we have two tables and the first one is the Customers table which stores detailed information about the customers of an organization. The other one is CustomerOrders that stores the order details of the clients. According to the database thiết kế, the CustomerOrders table must not contain any invalid customer data. To overcome this issue, we need to create a foreign key between Customers and CustomerOrders columns. The following illustration shows the thiết kế of these two tables:

This foreign key establishes referential integrity between Customers and CustomerOrders tables, thus, restricting the insertion of a new row when the CustomerId value of the inserted row does not match the ID column values of the Customers table.

For this example, the Customers table is the parent table and the CustomerOrders table is the child table.

  • Tip: What is a foreign key in SQL Server: It creates a backlink between parent and child table columns and foreign key references a primary key in the parent table.

At first, we will create a Customers table through the following query and we will populate some sample data:

1

2

3

4

5

6

7

8

9

CREATE

TABLE

Customers

(

ID

INT

PRIMARY

KEY

,

CustomerName

VARCHAR

(

50

)

,

CustomerAge

  

SMALLINT

,

CustomerCountry

  

VARCHAR

(

50

)

)

INSERT

INTO

[

dbo

]

.

[

Customers

]

(

[

ID

]

,

[

CustomerName

Xem Thêm  Học lập trình trực tuyến - phỏng vấn ở kms

]

,

[

CustomerAge

]

,

[

CustomerCountry

]

)

VALUES

(

1

,

И

‘Salvador’

,

23

,

И

‘Brazil ‘

)

INSERT

INTO

[

dbo

]

.

[

Customers

]

(

[

ID

]

,

[

CustomerName

]

,

[

CustomerAge

]

,

[

CustomerCountry

]

)

VALUES

(

2

,

И

‘Lawrence’

,

60

,

И

‘China ‘

)

INSERT

INTO

[

dbo

]

.

[

Customers

]

(

[

ID

]

,

[

CustomerName

]

,

[

CustomerAge

]

,

[

CustomerCountry

]

)

VALUES

(

3

,

И

‘Ernest’

,

38

,

И

‘India’

)

The following query will create the CustomerOrders table and the CustomerId column will be foreign key and it references the ID column of the Customers table. SQL Server automatically gives a name to the foreign key that will be created.

1

2

3

4

5

6

CREATE

TABLE

CustomerOrders

(

ID

INT

PRIMARY

KEY

,

OrderDate

DATETIME

,

CustomerID

INT

FOREIGN

KEY

REFERENCES

Customers

(

ID

)

,

Amout

  

BIGINT

,

)

When we want to insert a new row into the CustomerOrders table, the value of the CustomerID must match the values in the ID columns of the Customers table.

1

2

3

4

5

INSERT

INTO

[

dbo

]

.

[

CustomerOrders

]

(

[

ID

]

,

[

OrderDate

]

,

[

CustomerID

]

,

[

Amout

]

)

VALUES

(

1

,

CAST

(

’29-Apr-2019′

AS

DATETIME

)

,

1

,

968.45

)

INSERT

INTO

[

dbo

]

.

[

CustomerOrders

]

(

[

ID

]

,

[

OrderDate

]

,

[

CustomerID

]

,

[

Amout

]

)

VALUES

(

2

,

CAST

(

’10-May-2019′

AS

DATETIME

)

,

2

,

898.36

)

INSERT

INTO

[

dbo

]

.

[

CustomerOrders

]

(

[

ID

]

,

[

OrderDate

]

,

[

CustomerID

]

,

[

Amout

]

)

VALUES

(

3

,

CAST

(

’21-Oct-2019′

AS

DATETIME

)

,

3

,

47.01

)

 

SELECT

*

FROM

[

CustomerOrders

]

Result of the Customers table

The previous insert batch statement does not return any error because all CustomerID values match ID column values in the Customers tables.

Foreign key data matching illustration

The following query will return an error and the insert statement will be rolled back because the Customers table does not contain any row that has an ID value equal “4”:

1

2

INSERT

INTO

[

dbo

]

.

[

CustomerOrders

]

(

[

ID

]

,

[

OrderDate

]

,

[

CustomerID

]

,

[

Amout

]

)

VALUES

(

4

,

CAST

(

’29-Apr-2019′

AS

DATETIME

)

,

4

,

968.45

)

Foreign key constraint conflict error

If we want to give a name explicitly to the foreign key constraint, we can use the following query:

1

2

3

4

5

6

7

CREATE

TABLE

CustomerOrders

(

ID

INT

PRIMARY

KEY

,

OrderDate

DATETIME

,

CustomerID

INT

,

Amout

  

BIGINT

,

CONSTRAINT

FK_CustomerCheck

FOREIGN

KEY

(

CustomerID

)

  

REFERENCES

customers

(

ID

)

)

  • Tip: What is a foreign key in SQL Server: It is a constraint that provides referential integrity between two tables.

After creating a table, we can add a foreign key to this table. We can use the following query in order to add a foreign key to the existing table:

1

2

3

4

5

6

7

8

9

CREATE

TABLE

CustomerOrders

(

ID

INT

PRIMARY

KEY

,

OrderDate

DATETIME

,

CustomerID

INT

,

Amout

  

BIGINT

)

 

ALTER

TABLE

CustomerOrders

ADD

CONSTRAINT

FK_CustomerCheck

FOREIGN

KEY

(

CustomerID

)

  

REFERENCES

Customers

(

ID

)

Tip: The following query will help to find out more details about foreign key relations in the executed database.

1

2

3

4

5

6

7

SELECT

OBJECT_NAME

(

FK

.

referenced_object_id

)

AS

‘Referenced Table’

,

      

OBJECT_NAME

(

FK

.

parent_object_id

)

AS

‘Referring Table’

,

      

FK

.

name

AS

‘Foreign Key’

,

      

COL_NAME

(

FK

.

referenced_object_id

,

FKC

.

referenced_column_id

)

AS

‘Referenced Column’

,

      

COL_NAME

(

FK

.

parent_object_id

,

FKC

.

parent_column_id

)

AS

‘Referring Column’

FROM

sys

.

foreign_keys

AS

FK

    

INNER

JOIN

sys

.

foreign_key_columns

AS

FKC

ON

FKC

.

constraint_object_id

=

FK

.

OBJECT_ID

Finding foreign key details with query in a database

Foreign key cập nhật and delete rules

As we mentioned, the main purpose of the foreign key is to provide the referential integrity between parent and child table. Sometimes, we may need to cập nhật or delete data from the parent table. In this case, we have to decide the behavior of the child table data because it is referenced to the parent table. In the SQL Server, we can specify delete and cập nhật rules for the foreign keys so we can determine the behavior of the child data when we want to cập nhật or delete some data from the parent table.

  • Tip: What is a foreign key in SQL Server: The primary purpose of the foreign key is to establish control upon the data that will be inserted into the table, which involves foreign key. The inserted data must match the referenced table.

These rules are:

Delete Rules:

  • No Action: It returns an error when we want to delete any row from the parent table and the deleted statement will be rolled back
  • Cascade: In this option, the deleted statement also deletes all associated rows from the child table
  • Set Null: In this option, the deleted statement deletes the parent table row and associated values will be updated with null values on the child table. The foreign key column must be nullable
  • Set Default: The delete statement deletes parent table row and associated values of the child table will be updated with the default value of the foreign key column. In order to work this rule, a default constraint should be specified for the foreign key column and this default value must match in the parent table

Cập nhật Rules:

  • No Action: It returns an error when we want to cập nhật any row from the parent table and the cập nhật statement will be rolled back
  • Cascade: In this option, the updated statement also updates all associated rows from the child table
  • Set Null: In this option, the updated statement updates the parent table data and the associated values of the child table are updated to a default value. The foreign key column must be nullable
  • Set Default: The cập nhật statement updates the parent table row and child table associated values will be updated with the default value of the foreign key column. In order to work this rule, a default constraint should be specified for the foreign key column and this default value must match in the parent table

In the following example, we will create a CustomerSales table and this table CustomerId column referencing ID column of the Customers table. The delete rule of the foreign key will be specified as No Action and the cập nhật rule will be specified as Set Null option.

1

2

3

4

5

6

7

CREATE

TABLE

CustomerSales

(

ID

  

INT

PRIMARY

KEY

,

SaleDate

DATETIME

,

CustomerID

INT

FOREIGN

KEY

REFERENCES

Customers

(

ID

)

ON

UPDATE

CASCADE

  

ON

DELETE

NO

ACTION

,

SaleAmount

MONEY

,

)

Now, we will populate some data into this table:

1

2

3

INSERT

INTO

[

dbo

]

.

[

CustomerSales

]

(

[

ID

]

,

[

SaleDate

]

,

[

CustomerID

]

,

[

SaleAmount

]

)

VALUES

(

1

,

CAST

(

’05-Mar-2019′

AS

DATETIME

)

,

1

,

726.24

)

INSERT

INTO

[

dbo

]

.

[

CustomerSales

]

(

[

ID

]

,

[

SaleDate

]

,

[

CustomerID

]

,

[

SaleAmount

]

)

VALUES

(

2

,

CAST

(

’02-Nov-2019′

AS

DATETIME

)

,

2

,

817.33

)

INSERT

INTO

[

dbo

]

.

[

CustomerSales

]

(

[

ID

]

,

[

SaleDate

]

,

[

CustomerID

]

,

[

SaleAmount

]

)

VALUES

(

3

,

CAST

(

’13-Nov-2019′

AS

DATETIME

)

,

3

,

768.02

)

When we try to delete one row from the Customers (parent table), we will experience an error.

1

DELETE

FROM

Customers

WHERE

Id

=

2

What is the foreign key in SQL Server

As we can see, the foreign key rule prevents deleting the referenced column value. Now, we will cập nhật a row of the Customers (parent table value).

1

2

3

4

5

6

DELETE

CustomerOrders

SELECT

*

FROM

Customers

SELECT

*

FROM

CustomerSales

UPDATE

Customers

SET

ID

=

9999

WHERE

ID

=

1

SELECT

*

FROM

Customers

SELECT

*

FROM

CustomerSales

What is the foreign key in SQL Server

As we can see, updating the Customers (parent table) affects the CustomerSales (child table). Whereas, we did not do any changing on the CustomerSales table.

Conclusion

In this article, we tried to find an answer to “What is a foreign key in the SQL Server” question and we looked at the foreign key concept from different perspectives and gave various answers in the light of this information.

Viết một bình luận