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!
