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
|
No comments:
Post a Comment