SQL Constraints - Primary Key Constraint

Primary Key Constraint
• Primary Key Constraint is used to restrict duplicate values. While inserting data into a particular table, the PRIMARY KEY constraint uniquely identifies each record.
• Primary Key has to apply for a particular column in a table or can apply for a group of columns.
• If you apply for a single column, you can't enter one value more than once in that column.
How to define the primary key

Here <PrimaryKeyID> is not mandatory. But, if you do not define the ID, DB engine creates the primary key with its own ID.
Without define Primary key ID you can define the Primary key as below.

	Syntax	Example
	CREATE TABLE <Tbl_Name>	CREATE TABLE tbl_Employees
	(	(
Method1	Col1 Datatype NOT NULL PRIMARY KEY,	EmpID INT NOT NULL PRIMARY KEY,
	Col2 Datatype	EmpName VARCHAR(50)
	)	)
	CREATE TABLE <Tbl_Name>	CREATE TABLE tbl_Employees
	(	(
Method2	Col1 Datatype NOT NULL PRIMARY KEY,	EmpID INT NOT NULL,
	Col2 Datatype	EmpName VARCHAR(50)
	PRIMARY KEY (<Col_Name>)	PRIMARY KEY (EmpID)
	)	)

To find the primary Key ID

Database --> Tables -->(Expand by clicking on ‘+’ symbol which is visible in the left side of the table)

Keys (See the pics below)

EID	Ename	Edept
0	Sekhar	MIS
1	Naga	Accounts
2	Srinivas	SWD
3	Raja Sekhar	MIS
2	Ramesh	Accounts

Wrong,…..You can’t enter record again with Eid 2

If you apply the Primary key to EID column in the above table, you can’t repeat any value again in that column.

If you want to apply the primary key to a table which is already created, using alter command you can achieve that

Add Primary Key Using Alter Command:

	Syntax					Example
With ID	ALTER TABLE <table_name> ADD CONSTRAINT <PrimaryKeyID> PRIMARY KEY(Column_Name)					ALTER TABLE tbl_CheckPrimaryKey ADD CONSTRAINT pk_ID PRIMARY KEY(PKID)
Without ID	ALTER TABLE <table_name>  PRIMARY KEY(Column_Name)					ALTER TABLE tbl_CheckPrimaryKey ADD  PRIMARY KEY(PKID)

Remove Primary Key Using Alter Command:

Syntax	Example
ALTER TABLE tbl_CheckPrimaryKey DROP CONSTRAINT pk_ID	ALTER TABLE tbl_CheckPrimaryKey DROP CONSTRAINT pk_ID

To apply the Primary key to the group of columns.

	Syntax					Example
	CREATE TABLE <Tbl_Name>					CREATE TABLE tbl_Employees
	(						(
Method1	Col1 Datatype NOT NULL PRIMARY KEY,					EmpID INT NOT NULL,
	Col2 Datatype					EmpName VARCHAR(50)
	Col3 Datatype					EmpDept VARCHAR(50)
	PRIMARY KEY (<Col_Name1,Col_Name2,Col_Name3...>)					PRIMARY KEY (EmpID,EmpName)
	)					)
	CREATE TABLE <Tbl_Name>					CREATE TABLE tbl_Employees
	(					(
Method2	Col1 Datatype NOT NULL PRIMARY KEY,					EmpID INT NOT NULL,
	Col2 Datatype					EmpName VARCHAR(50)
	Col3 Datatype					EmpDept VARCHAR(50)
	Constraint <primarykey_ID> PRIMARY KEY  (<Col_Name1,Col_Name2,Col_Name etc...>)					Constraint <P_ID> PRIMARY KEY (EmpID,EmpName)
	)					)

We can apply primary Key to a group of columns; 

If we apply to group of columns, we can't enter values again with the same combination of rows.

See the example below.

EID	Ename	Edept
0	Sekhar	MIS
1	Naga	Accounts
2	Srinivas	SWD
3	Raja Sekhar	MIS
3	Mahesh	MIS
3	Raja Sekhar	MIS

Wrong, as already data exists there with the same combination of EID, Ename

Interview questions:

SNO	Question
1	What is a primary Key?
2	Can you enter null value in a primary Key?
3	Write the Primary key syntax
4	Can you define primary Key to more than one Column?
5	If you feel yes, write the syntax.
6	Can you add Primary Key to a table, which is already, exists? If yes write the syntax
7	Can you remove Primary key? If yes write the syntax