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
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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)
1 2 3 | 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
1 2 3 4 5 6 7 8 9 | 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.
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 6 7 8 9 | 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>
1 2 3 4 5 6 7 8 9 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | 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 |
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.
1 2 3 | 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.
1 2 3 4 5 | 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
1 2 3 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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
1 2 3 4 5 6 7 8 9 10 11 | 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.
1 2 3 4 5 6 7 8 | 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.
1 2 3 | 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.
