Create & Drop a Primary Key - SQL Query
i) How to create a primary key on only one field?
Answer:
CREATE TABLE Students
(
Stud_ID numeric(10) not null,
Stud_Name varchar2(50) not null,
CONSTRAINT Student_pk PRIMARY KEY (Stud_ID)
);
In the above example the primary key is created on the column 'Stud_ID'.
ii) How to create a primary key on multiple field from table students?
Answer:
CREATE TABLE Students
(
Stud_ID INT not null,
Stud_Name varchar2(50) not null,
CONSTRAINT Student_pk PRIMARY KEY (Stud_ID, Stud_Name)
);
In the above query a primary key is created on column 'Stud_ID' and Stud_Name Column. The not null constraint is used to avoid null values .
iii) How to create a primary key with ALTER TABLE statement?
Answer:
Consider that a table Student is already created as shown below.
CREATE TABLE Students
(
Stud_ID INT not null,
Stud_Name varchar2(50) not null
);
So, User wants to create a primary key on a specific column 'Stud_ID'.
The query should be written as:
ALTER TABLE Students
ADD CONSTRAINT Student_pk
PRIMARY KEY (Stud_ID');
The following query is used to drop primary key.
ALTER TABLE Students
DROP PRIMARY KEY;
