SQL SERVER Topic

PRIMARY KEY Constraint In SQL
PRIMARY KEY Constraint In SQL:-
  1. The PRIMARY KEY constraint uniquely identifies each record in a database table.
  2. Primary keys must contain UNIQUE values.
  3. A primary key column cannot contain NULL values.
  4. Most tables should have a primary key, and each table can have only ONE primary key.
Syntax:- 
  

CREATE TABLE tblEmployees
(
EmpID INT PRIMARY KEY,EmpName VARCHAR(100),Address varchar(200),MobileNo bigint,Email NVARCHAR(200)
)


How to create primary key by using multiple columns in SQL:-In the following example there is only ONE PRIMARY KEY (pk_EmpID). However, the VALUE of the primary key is made up of TWO COLUMNS (EmpID + MobileNo). 
  

CREATE TABLE tblEmployees
(
EmpID INT NOT NULL,
EmpName VARCHAR(100),
Address varchar(200),
MobileNo bigint,
Email NVARCHAR(200) 
CONSTRAINT pk_EmpID PRIMARY KEY (EmpID,MobileNo)
)


How to Alter PRIMARY KEY Constraint on TABLE in SQL:-

Note:- If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL (NOT NULL) values (when the table was first created). 

  

-----------------Alter Primary key Constraint for single column-----------------
ALTER TABLE tblStudents ADD PRIMARY KEY (StuID)


-----------------Alter Primary key Constraint for multiple columns-----------------

ALTER TABLE tblStudents ADD CONSTRAINT pk_StuID PRIMARY KEY (StuID,MobileNo)


How To DROP a PRIMARY KEY Constraint on table in SQL:-

  

ALTER TABLE tblStudents DROP CONSTRAINT pk_StuID