How to add Foreign key Constraint to existing table in SQL Server - SQL Server / TSQL Tutorial Part 68

Scenario:

You have already created two tables dbo.Customer and dbo.Orders. Primary Key is created on CustomerId column in dbo.Customer table.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )

CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    )

You need to alter dbo.Orders table and add Foreign key constraint for CustomerId from dbo.Customer.

Solution:

As the tables are already present, the below script can be used to alter dbo.Orders table and add Foreign Key constraint.

If the column already exists in second table on which you would like to create Foreign Key Constraint, you are good to run the below script. As we don't have the column in table, we are going to add CustomerId column in dbo.Orders table first. The column Name does not has to match with first table column in our case dbo.Customer.CustomerId

--Add new column to Table in case you don't have    
Alter table dbo.Orders
    Add CustomerId int
     
--Add Foreign Key Constraint on Existing Table
    Alter table dbo.Orders
    Add Constraint Fk_CustomerId  
    Foreign Key(CustomerId) References dbo.Customer(CustomerId)

Create Foreign Key Constraint on Composite Columns

The below script can be used to create Foreign Key Constraint for Composite Columns.

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)
    )


    CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,FirstName VARCHAR(100),
     SSN VARCHAR(10) Not Null
    )

Columns already exists in both tables so we don't have to add columns to second table. We only need to create Foreign Key Constrain. Below script can be used to create Foreign Key Constraint for composite columns.

     Alter table dbo.Orders
     Add Constraint Fk_Order_Customer_FName_SSN 
     FOREIGN KEY (FirstName,SSN) REFERENCES dbo.Customer(FName,SSN)

You will write you syntax for your table 

Alter table dbo.YourTableName

Add Constraint Constraint_Name

Foreign Key (Column1FromYourTableName,Column2FromYourTable) References dbo.YourFirstTable(Column1FromPrimaryKey,Column2FromPrimaryKey) 

Video Demo : How to add Foreign Key Constraint to existing Table in SQL Server