---
title: "How to implement Foreign Key Relationship in SQL Server?"  
description: "How to implement Foreign Key Relationship in SQL Server?"  
author: "Anubhav Sharma"  
published: 2026-04-27  
updated: 2026-04-27  
canonical: https://www.mindstick.com/interview/34491/how-to-implement-foreign-key-relationship-in-sql-server  
category: "database"  
tags: ["database", "sql server"]  
reading_time: 4 minutes  

---

# How to implement Foreign Key Relationship in SQL Server?

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

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

## Step 2: Create Child Table with Foreign Key

```plaintext
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

```plaintext
-- 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

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

## Cascade Options (Important)

You can control behavior on delete/update:

```plaintext
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

```plaintext
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

## Answers

### Answer by Anubhav Sharma

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

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

## Step 2: Create Child Table with Foreign Key

```plaintext
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

```plaintext
-- 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

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

## Cascade Options (Important)

You can control behavior on delete/update:

```plaintext
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

```plaintext
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


---

Original Source: https://www.mindstick.com/interview/34491/how-to-implement-foreign-key-relationship-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.