How to add Primary Key Constraint in SQL Server
A primary key is used to uniquely identify each record in a table. A table can have only one primary key. A primary key can consist of one or more fields/columns. A primary key with more than one column is called as composite key.
A combination of NOT NULL and UNIQUE Constraints is also called primary key.
For example, in a student’s table, each and every student have a unique id/roll number. We cannot have the same id/roll number for two students in a table.
Primary Key on Create Table Using T-SQL
Primary Key can be created at the time of table creation using T-SQL. We can create Primary Key just by writing PRIMARY KEY keyword after writing column name, and it’s data type. In the below example ‘EmpID INT PRIMARY KEY NOT NULL’ is the first column where EmpID is the PRIMARY KEY column.
CREATE TABLE tblEmployee ( EmpID INT PRIMARY KEY NOT NULL, EmpFirstName NVARCHAR(20) NULL, EmpLastName NVARCHAR(20) NULL, EmpEmail NVARCHAR(50) NULL, EmpPhone NVARCHAR(15) NULL, EmpDeptID INT NULL, EmpSalary INT NULL, EmpJoiningDate DATETIME NULL ) GO
Primary Key on Alter Table Using T-SQL
ALTER TABLE tblEmployee
ADD PRIMARY KEY (EmpID)
A Primary Key Constraint can also be created on alter table.
Adding Primary Key When Creating Table Using GUI(Graphical User Interface)
- Expand Databases, then expand database(HR for this demo) where you want to create table, and right click on Tables, go to New and then click on Table as shown in below image.
- In the Text Editor window, fill the table with required fields.
- To make the EmpID column as Primary Key, right-click on the left side arrow of EmpID column and click on Set Primary Key.
- Click on save button or right click on dbo. Table_1 tab and click on Save Table_1.
- Fill the pop window with table name and click on OK button.
- A table with the Primary Key Constraint will be created. You can check it in the Object Explorer by expanding the database where you created the table, then expand the Tables node and expand Keys node, primary key will be there.