• Introduction:

     

    In this tutorial we will learn how create foreign  key in SQL Server with example.

     

    Description:

     

    In SQL Server foreign key is a column (or columns) in one table that uniquely identifies a row of another table. In simpler words, the foreign key is defined in a second table, but it references to the primary key in the first table.

    Let us take an simple example to explain it:

    SQL FOREIGN KEY on CREATE TABLE

    First table

    • CREATE TABLE CUSTOMER
    • (
    • CUSTOMER_ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    • CUSTOMER_FNAME NVARCHAR(100),
    • CUSTOMER_MNAME NVARCHAR(100),
    • CUSTOMER_LNAME NVARCHAR(100),
    • CUSTOMER_ADDRESS NVARCHAR(500),
    • CUSTOMER_CONTACT NVARCHAR(100),
    • CUSTOMER_EMAIL NVARCHAR(100)
    • )
    • Second table

      Following in the orders table CUSTOMER_ID column, we create foreign key references to CUSTOMER_ID column to the customer table

    • CREATE TABLE ORDERS
    • (
    • ORDERID BIGINT IDENTITY(1,1) PRIMARY KEY,

      CUSTOMER_ID BIGINT FOREIGN KEY REFERENCES CUSTOMER (CUSTOMER_ID),

      ORDER_NUMBER BIGINT  

    • )
    •  SQL FOREIGN KEY constraint for ALTER TABLE:
    • If the Order table is already created and you want to create a FOREIGN KEY constraint on the CUSTOMER_ID column, you should write the following syntax:
    • ALTER TABLE ORDERS 
    • ADD CONSTRAINT fk_Orders  FOREIGN KEY(CUSTOMER_ID) 
    • REFERENCES CUSTOMER (CUSTOMER_ID)
    • DROP SYNTAX for FOREIGN KEY COSTRAINT:

      If you want to drop a FOREIGN KEY constraint, use the following syntax:

    • ALTER TABLE ORDERS 
    • DROP CONSTRAINT fk_Orders
    • Difference between primary key and foreign key in SQL:

              v  These are some main difference between primary key and foreign key in SQL-

              v  Primary key cannot be null but foreign key can be null.

              v  Primary key is always unique while foreign key can be duplicated.

              v  There is only one primary key in the table but we can have more than one foreign key in the table.

              v  By default primary key adds a clustered but foreign key does not automatically create an index, clustered or non-clustered. You must manually create an index for foreign key.

      Thanks.

Comments

  1. By:priyanka patel mac bhijan
    6/1/2019 5:17:27 AM

    very good



Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like