Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

interview

home / developersection / interviews / how to implement foreign key relationship in sql server?
Ask Question
Anubhav Sharma
Anubhav Sharma

Student

The Anubhav portal was launched in March 2015 at the behest of the Hon'ble Prime Minister for retiring government officials to leave a record of their experiences while in Govt service .

Message

1 Answers

Anubhav Sharma
Anubhav Sharma 27 Apr 2026
Report Abuse

A foreign key in Microsoft SQL Server is used to link two tables together and enforce data integrity. It ensures that values in one table must exist in another.

Basic Concept

  • Parent Table → contains Primary Key
  • Child Table → contains Foreign Key

Example:

  • Customers (Parent)
  • Orders (Child)

Step 1: Create Parent Table

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100)
);

Step 2: Create Child Table with Foreign Key

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATETIME,
    CustomerID INT,
    CONSTRAINT FK_Orders_Customers 
        FOREIGN KEY (CustomerID) 
        REFERENCES Customers(CustomerID)
);

How It Works

  • Orders.CustomerID must exist in Customers.CustomerID
  • You cannot insert an order with a non-existing customer
  • You cannot delete a customer if related orders exist (by default)

Insert Example

-- Valid
INSERT INTO Customers VALUES (1, 'John');
INSERT INTO Orders VALUES (101, GETDATE(), 1);

-- Invalid (Error)
INSERT INTO Orders VALUES (102, GETDATE(), 999);

Adding Foreign Key to Existing Table

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);

Cascade Options (Important)

You can control behavior on delete/update:

FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE
ON UPDATE CASCADE

Options:

  • CASCADE → delete/update child automatically
  • SET NULL → set FK to NULL
  • NO ACTION → prevent operation (default)

Check Foreign Keys in Database

SELECT 
    fk.name AS ForeignKey,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
    OBJECT_NAME(fk.referenced_object_id) AS ParentTable
FROM sys.foreign_keys fk;

Real-World Use Cases

  • Orders → Customers
  • Posts → Users
  • Comments → Posts

Best Practices

  • Always index foreign key columns
  • Use meaningful constraint names
  • Use cascade carefully (avoid accidental mass deletes)
  • Keep data types same in both tables
MindStick Training