Before stepping into this topic, Knowledge about the primary key is really important. Click here to learn about what is the primary key is.
Moving ahead, many times it happens that we want to add a primary key in an already existing table in a database. We, don't realize at the time of the creation of a table that a particular attribute can act as a primary key.
A simple method to solve this is to alter an existing table and add a primary key to it.
Follow the syntax below to perform the same task:
Syntax: Alter Table Add Primary Key Oracle
ALTER TABLE Table_Name ADD CONSTRAINT Constraint_name PRIMARY KEY (attribute);
Example : Oracle Alter Table Add Primary Key
We have a Stu table shown below as:
We realize that we want to make the Roll_no attribute the primary key. We can do it in the following way:
ALTER TABLE Stu ADD CONSTRAINT stu_pk PRIMARY KEY (Roll_No);
- Stu is the name of the Table.
- stu_pk is the name of the Constraint Name.
- Roll_No is the name of the attribute that we want to mark as a primary key.
Now, Let's learn how to alter two attributes of a single table and create it as a primary key. We can see in Stu Table that Roll_No and Stu_id are two columns and both of these attributes are capable enough to uniquely identify a table. So, both attributes can be made as a primary key. Follow the code for the same:
ALTER TABLE Stu ADD CONSTRAINT stu_pk PRIMARY KEY (Stu_id,Roll_No);