SQL Constraints - Primary Key Constraint - DotNet and DataBase

Wednesday, 1 August 2018

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








No comments:

Post a Comment

x

Get Updates On

Discussion updates

Straight Into Your INBOX!

Enter your email address to subscribe to this website and receive notifications of new posts by email.