SqlTutorial-1 : Lesson-2 : Class-2 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Integrity Constraints: Primary Key and Unique Key Constraints

1. Primary Key

• Primary key is used to uniquely identifying each row in the table.
• For a table there can one primary key. However, there is no primary key for weak entity.

Syntax:

[CONSTRAINT c_name]
PRIMARY KEY [CLUSTERED|NONCLUSTERED](col_name1 [{,col_name2} ...])

Examples:

(1) Defining Primary Key while table creation.

1. PRIMAY KEY as Column Constraints.

   CREATE TABLE Employee (
         emp_no int NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
         emp_name varchar(30)NOT NULL
   )

2. PRIMARY KEY as Table Constraints.

   CREATE TABLE Employee (
     emp_no INTEGER NOT NULL,
     emp_name CHAR(20) NOT NULL,
     CONSTRAINT emp_pk PRIMARY KEY (emp_no)
   )

3. Selecting IDENTITY column as PRIMARY KEY.

   CREATE TABLE MyTable (
       EmpId Int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ID PRIMARY KEY)

4. NONCLUSTERED PRIMARY KEY

   CREATE TABLE MyTable (
       MyID Int IDENTITY(1,1) NOT NULL,
       Description nVarChar(50) NOT NULL,
       CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED(MyID)
   )

5. Multi-column Primary Key.

   CREATE TABLE ClassGrades(
       ClassID int, StudentID int,
       GradeLetter varchar(2), Constraint PK_CG PRIMARY KEY(ClassID,StudentID)
   )

(2) Defining Primary Key constraints after table creation.

1. Adding PRIMAY KEY with ALTER TABLE

   CREATE TABLE employee(
      id INTEGER NOT NULL,
      name VARCHAR(20),
      salary DECIMAL(10,2),
      start_Date  DATETIME,
      region VARCHAR(10),
      city VARCHAR(20),
      managerid INTEGER
   );
   
   ALTER TABLE Employee 
       ADD CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (ID)

2. You cannot add another primary key to table

   CREATE TABLE MyTable (
      MyID  Int    IDENTITY(1,1) NOT NULL ,
      ID Int  NOT NULL,
      Description   nVarChar(50),
      Region  nVarChar(10) DEFAULT 'PNW',
      CONSTRAINT MyTable_PK PRIMARY KEY NONCLUSTERED (MyID,Region))
   GO
   
   ALTER TABLE MyTable 
       ADD CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED (MyID) // Error 
   GO

2. Unique Key

• Unique-Key prevents duplicate values in a column. It can include Null Values.
• A table can exists multiple Unique-keys.
• You can modify values of Unique-key.

Syntax:

[ COSNTRAINTS constraint_Name] UNIQUE (column[ASC | DESC] [,…n])

Examples:

1. Adding UNIQUE constraints as column constraint.

   CREATE TABLE Shippers (
      ShipperID int IDENTITY NOT NULL PRIMARY KEY,
      PhoneNo varchar(14) NOT NULL UNIQUE,
      ShipperName varchar(30) NOT NULL,
      Address varchar(30) NOT NULL,
      City varchar(25) NOT NULL,
      State char(2) NOT NULL,
      Zip varchar(10) NOT NULL, )

2. Adding UNIQUE constraints as Table constraint.

   CREATE TABLE Employee (
      EmployeeID Int IDENTITY(1,1) NOT NULL,
      LastName nVarChar(50) NOT NULL,
      FirstName nVarChar(50) NOT NULL,
      SSN Char(9) NOT NULL,
      CONSTRAINT U_SSN UNIQUE NONCLUSTERED (SSN) 
   )

3. Adding UNIQUE constraint to existing table.

   ALTER TABLE Employee
      ADD CONSTRAINT u_SSN UNIQUE NONCLUSTERED (SSN);

4. Adding UNIQUE Constraints on composite column.

   CREATE TABLE customer_location (
         cust_id int NOT NULL,
         cust_location_number int NOT NULL,
         CONSTRAINT cus_loc_unq UNIQUE(cust_id, cust_location_number) )

3. Primary Key Vs Unique Key

The primary difference is Nullability and Indexing. Following are some differnces:

• Behavior: Primary Key is used to identify a row(record) in a table whereas Unique-Key is to prevent duplicate values in a column.
• Nullability: Primary key does not include Null values whereas Unique-key can.
• Indexing: By default Sql-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key.
• Existence: A table can have at most one primary key but can have multiple Unique-key.
• Modifiability: You can’t change or delete primary values but Unique-key values can.

UNIQUE + NOTNULL!=PRIAMRY key Unique is always refers Non-clustered index and primary is always clustered.