SQL-Server – Primary Key and Foreign Key in Tables

Primary Key and Foreign Key in Tables

How to create a table with a Primary Key and Foreign Key using T-SQL statements? Actually there are two methods to do so.

Method 1 – At the time of Table creation

We can assign the column as Primary Key and Foreign Key at the time of table creation.

— Create table Department with DeptID as Primary Key

CREATE TABLE Department(

DeptID INT PRIMARY KEY,

DeptName VARCHAR(25)

)

— Create table Employee with EmpID as Primary Key, and DeptID as Foreign Key

CREATE TABLE Employee(

EmpID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DeptID INT FOREIGN KEY REFERENCES Department(DeptID),

Salary INT,

ProjectCode INT

)

Method 2: After Table Creation

If the table already exists and we need to assign a column as Primary Key or Foreign Key, we can do it using ALTER statement. Make sure that column you are selecting is not nullable column.

— Make DeptID of table Department as Primary Key

ALTER TABLE Department ADD PRIMARY KEY (DeptID)

— Make EmpID of table Employee as Primary Key

ALTER TABLE Employee ADD PRIMARY KEY (EmpID)

— Make DeptID as Foreign Key in table Employee

ALTER TABLE Employee ADD CONSTRAINT DeptID_FK

FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

Good Luck!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: