How to create Foreign Key Constraint on Multiple Columns in SQL Server - SQL Server / TSQL Tutorial Part 67

Scenario: 

You are working as SQL Server developer, you need to create a table dbo.Customer with composite primary key by using columns FName and SSN. One you are done with creating primary key in dbo.Customer table, you need to create second table dbo.Orders and create foreign key constraint by using Primary Key columns.

Solution:

Let's create the dbo.Customer table with composite primary key by using below script.


USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT Identity(1,1)
    ,FName VARCHAR(100) Not Null
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10) Not Null,
    Constraint Pk_FName_SSN Primary Key (FName,SSN)
    )




Notice that we have use Constraint Constraint_Name Primary Key(Column1, Column2) as highlighted in green to create Composite Primary Key.

Let's create the dbo.Orders table by using below script.


CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,FirstName VARCHAR(100),
     SSN VARCHAR(10) Not Null,
     Constraint Fk_Order_Customer_FName_SSN 
     FOREIGN KEY (FirstName,SSN) REFERENCES dbo.Customer(FName,SSN)
    )




To create Foreign Key Constraint with multiple columns you will be using script as highlighted in green. 
You will say 
Constraint Constraint_Name Foreign Key(Column1,Column2) References dbo.PrimaryKeyTable(PrimaryKeyColumn1,PrimaryKeyColumn2)

You can see that In dbo.Orders, I have columns FirstName instead of FName that I have in dbo.Customer table. It means that you don't have to have the same column name in both tables when you are creating foreign key reference.


Let's insert a records in each of table and see if all working fine with Foreign Key Constraint.
     INSERT INTO dbo.Customer 
    (FName, LName,SSN)
     values
    ('Aamir','Shahzad','000-000-01')

    INSERT INTO dbo.Orders
    (OrderItemName,OrderItemAmt,FirstName,SSN)
    values ('TV',1,'Aamir','000-000-01')


Records are inserted successfully. Let's verify by using Select query
How to create Foreign Key Constrain on Multiple Columns in SQL Server Table


Let's try to insert a value in dbo.Orders which does not exists in dbo.Customer. It should through us as error due to Foreign Key constraint.

    INSERT INTO dbo.Orders
    (OrderItemName,OrderItemAmt,FirstName,SSN)
    values ('TV',1,'Aamir','000-000-02')

As highlighted SSN value does not exists in dbo.Customer, we got below error.

Msg 547, Level 16, State 0, Line 30
The INSERT statement conflicted with the FOREIGN KEY constraint "Fk_Order_Customer_FName_SSN". The conflict occurred in database "YourDatabaseName", table "dbo.Customer".
The statement has been terminated. 



*** The order of columns should be same what you have in primary key when we create foreign key constraint. If I try to create foreign key constraint with different order, I will get below error.

    CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,FirstName VARCHAR(100),
     SSN VARCHAR(10) Not Null,
     Constraint Fk_Order_Customer_FName_SSN 
     FOREIGN KEY (SSN,FirstName) REFERENCES dbo.Customer(SSN,FName)
    )


Msg 1776, Level 16, State 0, Line 13
There are no primary or candidate keys in the referenced table 'dbo.Customer' that match the referencing column list in the foreign key 'Fk_Order_Customer_FName_SSN'.
Msg 1750, Level 16, State 0, Line 13
Could not create constraint or index. See previous errors.



Video Demo : How to create Foreign Key Constraint on Multiple Columns in SQL Server







No comments:

Post a Comment