SQL Primary key Constraint :
In my previous articles i have given the idea about different sql constraints like Not Null Constraint , Unique Constraint ,Check Constraints E.t.c.This article gives you idea about the SQL Primary key Constraint.I will try to give the real industry examples of how the primary key is created and how to drop the primary key as well. This article on SQL Primary key Constraint will give you the idea about the primary key and user will able to do all the tasks related to SQL Primary key Constraint.
The SQL PRIMARY KEY Constraint Uniquely identifies each record in a database table.
How to add SQL Primary key Constraints?
PRIMARY KEY must contain unique values. A Primary Key column cannot contain NULL values. Each table can have only ONE PRIMARY KEY.
Real life Example:
CREATE TABLE Student
(
RollNo Number (10) PRIMARY KEY,
FName Varchar2 (15),
LName Varchar2 (15),
Location Varchar2 (10)
);
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY Constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Student
(
RollNo Number (10),
FName Varchar2 (15),
LName Varchar2 (15),
Location Varchar2 (10),
CONSTRAINT PK_Student_FName_LName PRIMARY KEY (FName, LName)
);
Note: In the example above there is only ONE PRIMARY KEY (PK_Student_FName_LName). However, the value of the PK_Student_FName_LName is made up of two columns (FName and LName).
To DROP a PRIMARY KEY Constraint:
To Drop a Primary Key Constraint, use the following SQL:
Syntax:
ALTER TABLE <Table_Name>
DROP CONSTRAINT <Constraint_Name>;
Example:
ALTER TABLE Student
DROP CONSTRAINT SYS_C005063;
ALTER TABLE Student
DROP CONSTRAINT PK_Student_FName_LName;
Note: The above statement will drop the Primary Key Constraint ‘PK_Student_FName_LName’ from the ‘Student’ Table.
SQL PRIMARY KEY Constraint on ALTER TABLE:
To create a PRIMARY KEY Constraint on the “RollNo” column when the table is already created, use the following SQL:
Syntax:
ALTER TABLE <Table_Name>
ADD <Constraint_Type> (Column_Name);
Example:
ALTER TABLE Student
ADD PRIMARY KEY (RollNo);
OR
ALTER TABLE Student MODIFY RollNo Number (10) Primary Key;
ALTER TABLE Student MODIFY RollNo Primary Key;
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE <Table_Name>
ADD CONSTRAINT <Constraint_Name> CONSTRAINT_TYPE (Column_Name);
Example:
ALTER TABLE Student3A
ADD CONSTRAINT PK_Student3A_FName_LName PRIMARY KEY (FName, LName);
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 values (when the table was first created).
To find Selected Tables from a User:
SELECT Table_Name FROM User_Tables;
SELECT Table_Name FROM User_Tables WHERE Table_Name LIKE ‘STU%’;
To find Selected Tables which have Constraints:
SELECT Table_Name, Constraint_Name, Constraint_Type, Status, Generated
FROM User_Constraints
WHERE Table_Name LIKE ‘STU%’;
Hope this article will give you idea about the SQL Primary key constraint with multiple examples. The purpose of this article is to give the proper idea with set of examples to the user so that user will be able to add the SQL Primary key Constraint with ease.This article not only gives the theoretical idea about the primary key but also the practical idea of SQL Primary key Constraint which is mostly used constraint in oracle for unique value representation.If you like this article or if you have any suggestions dont forget to add the comments in comments section.