How to Create Table with Foreign Key Constraint in SQL Server - SQL Server / TSQL Tutorial Part 66

What is Foreign Key in SQL Server Table:

Foreign Key in a table is a column or group of columns that provides a link between data in two tables. The Foreign Key in a table points to the primary key in another table.

Let's create dbo.Customer Table with Primary Key by using below DDL statement

USE YourDatabaseName
GO

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

As you can see that Customerid is the primary key in dbo.Customer Table.

As we are ready with our first table and have the Primary Key, we are good to go and create second table with foreign Key Constraint.

CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,Customer_id INT FOREIGN KEY REFERENCES Customer(CustomerId)
    )



You can see that we have added Customer_id column in dbo.Orders table that references to Customerid in dbo.Customer Table.

The primary key in first table is Customerid and Foreign Key Column in second table is Customer_id, that means the columns don't have to have same names. It is good idea to keep the same name so when you write queries and join the tables, you don't have to take a look which is primary key and which is foreign key, instead you would know that the same column names are in relationship.

Let's insert couple of records and see how Foreign Key Constraint works.

    insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (1,'Aamir','Shahzad','000-000-00')

    insert into dbo.Orders
    (OrderItemName,OrderItemAmt,Customer_Id)
    values ('TV',1,1)


Both records will be inserted just fine, as we are inserting same customerid in dbo.Orders that does exists in dbo.Customer.

If we will try to insert any value in dbo.Orders that is not present in dbo.Customer(CustomerId), It will through an error due to foreign key constraint.

 insert into dbo.Orders
    (OrderItemName,OrderItemAmt,Customer_Id)
    values ('TV',1,2)

When we run above query, it through error as we don't have 2 as Customerid in dbo.Customer table.

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


Video Demo : What is Foreign Key Constraint and How to create Foreign Key Constraint in SQL


No comments:

Post a Comment