Foreign Key in SQL Server - Tektutorialshub

Foreign key in SQL Server is used to establish the relationship between two tables. In this tutorial, we will learn what is Foreign Key ?. How to create a Foreign Key constraint. How to add it to an existing table. Learn how to Drop the it etc.

Table of Contents

What is Foreign Key
Creating a Foreign Key
Naming the Foreign key Constraint
Creating Composite Foreign Key
Finding the Foreign Key Name
Dropping Foreign Key
Adding Foreign Key to Existing Table
Rules & Limitations

What is Foreign Key

A foreign key in SQL Server is a column or group of columns in a table that corresponds to or references a primary key or a Unique key in another table.

Consider the following Customer & Invoice tables

CREATE TABLE Customer (
    CustomerID  [int] NOT NULL ,
    Name varchar(10) NOT NULL,
    PRIMARY KEY (CustomerID)
)

insert into Customer 
values (1,'Olive Yew'),
       (2,'Aida  Bugg')


CREATE TABLE Invoice (
    InvoiceID [int]  NOT NULL,
    CustomerID  [int]  NOT NULL,
    Amount   [decimal](18,2)  NOT NULL,
    PRIMARY KEY (InvoiceID),
)

insert into Invoice 
values (1,1,1000),
       (2,2,2000)

We save the information about the customer in the Customer table, with customerID as the Primary key.

The Invoices of the customer are stored in a separate Invoices table with InvoiceID uniquely identifying each Invoice. We add the CustomerID in the Invoice table to link each Invoice to the Customer.

Since the CustomerID in the Invoice table links each Invoice to a Customer, we call CustomerID as Foreign Key in Invoice Table.

In this relationship the customer table is the Parent Table (or Principal table). The Invoice table, which depends on the customer table is child table (or dependent table)

The invoice table must not contain any invalid customer data. For Example, if we run this query against the above tables it will succeed although it contains the invalid CustomerID. ( CustomerID 5 does not exist in Customer Table)

insert into Invoice values (3,5,5000)

This is where the Foreign Key constraint steps in. Using Foreign key we declare the relationship between two tables and SQL Server enforces that relationship

Creating a Foreign Key

The following is the basic Syntax for creating a Foreign Key inline. Using inline syntax you can only create Foreign Key on a single fields and also you cannot name it

CREATE Table <ChildTable> (
    ...
    ... 
    childcolumnName [datatype] REFERENCES <parentTable>(ParentColumnName)
    ...
    ...
)

The following query creates a Foreign Key Constraint on CustomerID column of Invoice Table. It refers to the CustomerID field of the Customer Table.

CREATE TABLE Invoice (
    InvoiceID   [int]  NOT NULL,
    CustomerID  [int]  NOT NULL REFERENCES Customer(CustomerID),
    Amount      [decimal](18,2) NOT NULL,
    PRIMARY KEY (InvoiceID),
)

You can also define the Foreign key on table level. It starts with the Keyword FOREIGN KEY followed by child column on which you want to apply foreign key constraint.

CREATE TABLE Invoice (
    InvoiceID   [int]  NOT NULL,
    CustomerID  [int]  NOT NULL ,
    Amount      [decimal](18,2) NOT NULL,
    PRIMARY KEY (InvoiceID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
)

Naming the Foreign key Constraint

The following query how you assign can name to the foreign key constraint. The name is needed when you want to drop the foreign key. Always follow a proper naming convention to name foreign key. You can use the convention FK_<childTableName>_<ParentTableName>_<childCol1>

CREATE TABLE Invoice (
    InvoiceID   [int]  NOT NULL,
    CustomerID  [int]  NOT NULL ,
    Amount      [decimal](18,2) NOT NULL,
    PRIMARY KEY (InvoiceID),
    CONSTRAINT FK_Invoice_Customer_CustomerID  FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
)

Creating Composite Foreign Key

The following script shows how to create a composite Foreign Key.

CREATE TABLE TableA (
    TableID1  [int] NOT NULL ,
    TableID2  [int] NOT NULL ,
    PRIMARY KEY (TableID1,TableID2)
)

CREATE TABLE TableB (
    TableBID    [int]  NOT NULL,
    ID1  [int]  NOT NULL ,
    ID2  [int]  NOT NULL ,
    PRIMARY KEY (TableBID),
    CONSTRAINT FK_TableB_TableA_ID1_ID2 FOREIGN KEY (ID1,ID2) REFERENCES TableA(TableID1,TableID2)
)

Finding the Foreign Key Name

You can use the following query to find out the FK of a particualr table.

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
Where tab1.name='TableB'    
//Change Table Name abive or Remove this line to find out all the FK in the database


***Result***

FK_NAME                   schema_name table   column  referenced_table referenced_column
------------------------  ----------- ------  ------  ---------------- ------------------
FK_TableB_TableA_ID1_ID2  dbo         TableB  ID1     TableA           TableID1
FK_TableB_TableA_ID1_ID2  dbo         TableB  ID2     TableA           TableID2

Source

Dropping Foreign Key

First find out the name of the Foreign key using the query from the previous section. Use the Alter table query to drop the Foreign key.

Alter Table TableB Drop CONSTRAINT FK_TableB_TableA_ID1_ID2

Adding Foreign Key to Existing Table

You can add Foreign Key using the Alter Table Syntax.

Alter Table Invoice  
  Add CONSTRAINT FK_Invoice_Customer_CustomerID 
   FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)

You can also use the following query to creating a Foreign Key without naming it. But beware SQL Server will not warn you if you run the query multiple times. It just create the duplicate Foreign Keys

Alter Table Invoice Add FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)

Rules & Limitations

There are few important rules & Limitations that you must know when creating a Foreign key.

The Data type of Both Fields must match

The Data type of both parent & child tables must match. The following query fails becuase data type of CustomerID in the invoice table is varchar which is different from Customer table.

CREATE TABLE Customer (
    CustomerID  [int] NOT NULL ,
    Name varchar(10) NOT NULL,
    PRIMARY KEY (CustomerID)
)

CREATE TABLE Invoice (
    InvoiceID [int]  NOT NULL,
    CustomerID  varchar(10)   NULL REFERENCES Customer(CustomerID),
    Amount   [decimal](18,2)  NOT NULL,
    PRIMARY KEY (InvoiceID),
)


*** Error ***
Column 'Customer.CustomerID' is not the same data type as referencing column 'Invoice.CustomerID' in foreign key 'FK__Invoice__Custome__740F363E'

The columns names do not have to match. Only data types must match.

CREATE TABLE Customer (
    CustomerID  [int] NOT NULL ,
    Name varchar(10) NOT NULL,
    PRIMARY KEY (CustomerID)
)

CREATE TABLE Invoice (
    InvoiceID [int]  NOT NULL,
    CustID   [int]   NULL REFERENCES Customer(CustomerID),
    Amount   [decimal](18,2)  NOT NULL,
    PRIMARY KEY (InvoiceID),
)

Foreign Key on UNIQUE Columns

Foreign keys can not only refer Primary keys but also Unique columns. In the example below we define CustomerID as UNIQUE column instead of Primary Key. You can still create a Foreign key constraint on CustomerID of invoice table.

CREATE TABLE Customer (
    CustomerID  [int] NOT NULL UNIQUE ,
    Name varchar(10) NOT NULL
)

CREATE TABLE Invoice (
    InvoiceID   [int]  NOT NULL,
    CustomerID  [int]  NOT NULL ,
    Amount      [decimal](18,2) NOT NULL,
    PRIMARY KEY (InvoiceID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) 
)

Foreign key columns can be NULL

You can create a Foreign key constraint on Nullable columns. It is unlike Primary key which does not allow NOT NULL columns.

Does not create any index

SQL Server does not create any index on Foreign key columns. You need to manually create an index on a foreign key table if you uses these columns SQL joins

Foreign key can refer to the same Table

The Employee table below has a ManagerID field, which refer to Manager’s EmployeeID. Here Employee table is both parent & child table.

The following query creates FK on ManagerID which refers to the EmployeeID


CREATE TABLE Employee (
    EmployeeID  [int] NOT NULL ,
    Name varchar(10) NOT NULL,
    ManagerID  [int] NOT NULL ,
    PRIMARY KEY (EmployeeID),
    CONSTRAINT FK_Employee_Employee_ManagerID 
        FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID)
)

Infact SQL Server allows a FK column to refer to itself. In the following example column TableAID refers to itself.

CREATE TABLE TableA (
    TableAID  [int] NOT NULL ,
    SomeField varchar(10) NOT NULL,
    PRIMARY KEY (TableAID),
    FOREIGN KEY (TableAID) REFERENCES TableA(TableAID)
)

Always name your Foreign Keys

You should always name your Foreign Keys using a naming convention.

The query below creates a Foreign Key CustomerID on invoice table. Run this query several times and each time SQL server creates a new Foreign key. It does not throw any errors.

Alter Table Invoice Add FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)

But if you the same name to create a constraint, SQL Server will throw an error.

Read More

Primary Key

Unique Key