Loading

Microsoft SQL Server

What is Primary Key and Foreign key Relationship Between Multiple Tables in SQL Server?. The Complete Microsoft SQL Server Developer Course 2022 [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 watch 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
CREATE TABLE Customer
(
Cid INT PRIMARY KEY,
Cname VARCHAR(40),
Cmobno CHAR(10)
)
Inserting Some Value
SELECT * FROM Customer

How to make Primary Key and Foreign key relationship between more than two tables

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
CREATE TABLE Products
(
Pcode INT PRIMARY KEY,
Pname VARCHAR(20),
Price MONEY
)
Inserting Some Value
SELECT * FROM Products

In this Video I am going to discuss How to make Primary Key and Foreign Key relationship between more than two tables in SQL Server.

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
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
SELECT * FROM Orders

In this Video I am going to discuss How to make Primary Key and Foreign Key relationship between more than two tables in SQL Server

How to add Constraint to an existing table?

SyntaxALTER 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.

See All

Comments (506 Comments)

Submit Your Comment

See All Posts

Related Posts

Microsoft SQL Server / Blog

What is SQL Server?

The SQL Server is a relational database management system (RDBMS) which is developed by Microsoft. It is also called MS SQL Server. This product is built on the basic function of storing, retrieving data as required by other applications. It can be run either on the same computer or on another across a network.
15-Feb-2022 /26 /506

Microsoft SQL Server / Blog

How to Creating Altering and Deleting Database in SQL Server?

In this article, I am going to discuss Creating Altering and Deleting Database in SQL Server with examples. Please read our previous article where we discussed how to connect to SQL Server Database using a client tool called SQL Server Management Studio. As part of this article, we are going to discuss the following pointers.
15-Feb-2022 /26 /506

Microsoft SQL Server / Blog

How to Creating Altering and Deleting Tables in SQL server?

In this article, I am going to discuss Creating Altering and Deleting Tables in SQL Server with examples. Along the way, we will also discuss the Data Definition Language in detail. Please read our previous article before proceeding to this article where we discussed the Creating Altering and Deleting Database in SQL Server with examples. As part of this article, we are going to discuss the following important points.
15-Feb-2022 /26 /506