What is Primary Key and Foreign key Relationship Between Multiple Tables in SQL Server?. The Complete Microsoft SQL Server Developer Course 2023 [Videos].

In this Video, 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 Videos before proceeding to this Video.

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

Inserting Some Value

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

Creating the Products table

Inserting Some Value

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

Creating The Orders Table

Inserting Some Value

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.