Primary Key and Foreign key Relationship Between Multiple Tables in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Primary Key and Foreign key relationship between Multiple Tables in SQL Server

In this article, I am going to discuss How to make Primary Key and Foreign key relationships between more than two tables in SQL Server. Please read our Primary Key and Foreign Key articles before proceeding to this article.

How to make Primary Key and Foreign key relationship between more than two tables in SQL Server:

Let us understand this with an example. Create a table name as Customer using Primary Key constraint and insert some values into Customer table.

Creating the Customer Table

CREATE TABLE Customer 
( 
    Cid    INT PRIMARY KEY, 
    Cname  VARCHAR(40), 
    Cmobno CHAR(10) 
)

Inserting Some Value

INSERT INTO Customer VALUES (1, 'AA', '9853977973') 
INSERT INTO Customer VALUES (2, 'BB', '8895558077') 
INSERT INTO Customer VALUES (3, 'CC', '7021801173') 

SELECT * FROM Customer

Next, create a 2^nd table with the name as Products using Primary Key Constraint and insert some values into the Products table.

Creating the Products table

CREATE TABLE Products 
( 
    Pcode INT PRIMARY KEY, 
    Pname VARCHAR(20), 
    Price MONEY 
) 

Inserting Some Value

INSERT INTO Products VALUES (10,'C',500) 
INSERT INTO Products VALUES (20,'C++',1000) 
INSERT INTO Products VALUES (30,'.NET',3500) 
INSERT INTO Products VALUES (40,'SQL',1800) 

SELECT * FROM Products

Now create a 3^rd table with the name as Orders by using Foreign Key references and some reference values in the ORDERS table.

Creating The Orders Table

CREATE TABLE Orders 
( 
    Odid     INT PRIMARY KEY, 
    Ordate   DATE, 
    Quantity INT, 
    Cid      INT FOREIGN KEY REFERENCES Customer(Cid), 
    Pcode    INT FOREIGN KEY REFERENCES Products(Pcode) 
) 

Inserting Some Value

INSERT INTO Orders VALUES (101,'2017/12/20',9,1,10) 
INSERT INTO Orders VALUES (102,'2017/12/20',10,2,10) 
INSERT INTO Orders VALUES (103,'2017/12/21',6,3,20) 
INSERT INTO Orders VALUES (104,'2017/12/22',11,1,40) 
INSERT INTO Orders VALUES (105,'2017/12/23',8,1,30) 

SELECT * FROM Orders

How to add Constraint to an existing table?

Syntax: ALTER TABLE<TABLE NAME>ADD CONSTRAINT<KEY VARIABLE NAME><CONSTRAINT KEY> (COLUM NAME)

Case1: Adding a primary key constraint on an existing column in the table.

Note: Before adding the primary key constraint to the table first we need to make the column NOT NULL and later add a primary key to that column.

Example: First, create the following table without constraint

CREATE TABLE EMP(EMPID INT, ENAME VARCHAR(30), SALARY MONEY)

Before adding the primary key constraint we need to make it NOT NULL like below

ALTER TABLE EMP ALTER COLUMN EMPID INT NOT NULL

Now we can add the primary key to the column like below

ALTER TABLE EMP ADD CONSTRAINT X PRIMARY KEY (EMPID)

Now the EMPID column contains the primary key.

Note: Before adding the primary key to the existing column we need to be sure that the column should not contain any duplicate values. If the column contains any duplicate value then we cannot add the primary key constraint to that column. The constraint key variable name can be any user-defined name.

Case2: Adding a unique constraint to an existing column in the table.

ALTER TABLE EMP ADD CONSTRAINT Y UNIQUE (ENAME)

Case3: Adding CHECK constraint to an existing column.

ALTER TABLE EMP ADD CONSTRAINT z CHECK (SALARY > 8000)

Case4: Adding a FOREIGN KEY constraint to an existing column.

Let create another table with the name DEP as Below

CREATE TABLE DEP(DNO INT, DNAME VARCHAR(30), EID INT)

Now we can make the EID column of the DEP table ass FOREIGN KEY because the EID column is the primary key in the EMP column.

ALTER TABLE DEP ADD CONSTRAINT Q FOREIGN KEY (EID) REFERENCES EMP(EMPID)

How to remove constraints from an existing table?

Syntax to drop:

ALTER TABLE<TABLENAME> DROP CONSTRAINT<KEY VARIABLE NAME>

Example:

ALTER TABLE EMP DROP CONSTRAINT Y

ALTER TABLE EMP DROP CONSTRAINT Z

Note: While dropping the primary key constraint we first need to drop the foreign key and then only we can delete the primary key.

In the next article, I am going to discuss the Cascading Referential Integrity Constraint in SQL Server. Here, in this article, I try to explain How to make Primary Key and Foreign Key relationships between more than two tables in SQL Server with Examples.