• 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:
    • These are some main difference between primary key and foreign key in SQL-
    • Primary key cannot be null but foreign key can be null.
    • Primary key is always unique while foreign key can be duplicated.
    • There is only one primary key in the table but we can have more than one foreign key in the table.
    • 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.

0 comments


Leave a comment

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

You may also like