Monday , September 23 2019
Home / SQL / Add PRIMARY KEY constraint on Existing Table by ALTER TABLE statement

Add PRIMARY KEY constraint on Existing Table by ALTER TABLE statement

You can define PRIMARY KEY constraint on existing table by ALTER TABLE statement.

Practice:

This practice will cover how to define PRIMARY KEY constraint on a column after creating a table by ALTER TABLE statement.

Syntax:

ALTER   TABLE   table_name   ADD   CONSTRAINT pk_constraint_name  PRIMARY KEY (primary_key_column_name);

Here

primary_key_column_name is the name of table column on which PRIMARY KEY constraint will be defined.

Example:

Step-1: Drop the PRODUCTINFO table if already exists.

DROP TABLE ProductInfo;

Step-2: Create the table without PRIMARY KEY constraint.

CREATE   TABLE   ProductInfo

(

Product_Id         Varchar2(10),

Product_Name  Varchar2(50),

Product_Type    Varchar2(50),

Unit                      Varchar2(30)

);

See the table structure.

DESCRIBE ProductInfo;

Step-3:

Execute the SELECT statement to confirm whether any data exists or not.

SELECT * FROM ProductInfo;

Result of the SELECT statement:

no rows selected

That means that the table is empty.

Step-4: Insert  two rows.

INSERT   INTO  ProductInfo (Product_Id, Product_Name, Product_Type,Unit)  VALUES  (‘P-101′,’SOAP’,’LUX’,’PCS’);

INSERT   INTO  ProductInfo (Product_Id, Product_Name, Product_Type,Unit)   VALUES  (‘P-102’, ‘SHAMPOO’, ‘ALL CLEAR’,’PCS’);

Step-5:

Now query  to see the resultset..

SELECT * FROM ProductInfo;

Result set of the SELECT statement:

PRODUCT_ID  PRODUCT_NAME PRODUCT_TYPE     UNIT

P-101                    SOAP                    LUX                       PCS

P-102                    SHAMPOO           ALL CLEAR            PCS

There are two rows in the table.

Step-6: Now try to define PRIMARY KEY constraint on existing table by ALTER TABLE statement. The statement to be executed:

ALTER TABLE ProductInfo ADD CONSTRAINT pk_pid UNIQUE (Product_Id);

In the above ALTER TABLE statement, the PRIMARY KEY constraint named pk_pid has been defined on the PRODUCT_ID column of PRODUCTINFO table that already exists in the database.

This is how; we can define PRIMARY KEY constraint on an existing table column.

Note: A PRIMARY KEY constraint can be created on multiple columns. To do this, you have to run the following statement:

ALTER TABLE ProductInfo ADD CONSTRAINT pk_pid_ptype PRIMARY KEY (Product_Id, Product_Type);

In the above ALTER TABLE statement, a PRIMARY KEY constraint named pk_pid_ptype has been created with two columns together (PRODUCT_ID and PRODUCT_TYPE) of PRODUCTINFO table.

Now insert row by executing the following INSERT statement.

INSERT INTO ProductInfo (Product_Id, Product_Name, Product_Type, Unit)  VALUES (‘P-201’, ‘SOAP’,’LUX’,’PCS’);

Try to execute the above INSERT statement again without any change in the two column (PRODUCT_ID and PRODUCT_TYPE) values where a PRIMARY KEY constraint has been created. Then the system will generate the following error.

INSERT INTO ProductInfo (Product_Id, Product_Name, Product_Type,Unit) VALUES (‘P-201′,’SOAP’,’LUX’,’PCS’)

Error at line 1

ORA-00001: unique constraint (PERVEJ.PK_PID_PTYPE) violated

Now change one column value. Suppose we have changed the value of PRODUCT_TYPE column. But the PRODUCT_ID column value (‘P-201’) has still unchanged. Execute the following statement.

INSERT INTO ProductInfo (Product_Id, Product_Name, Product_Type,Unit) VALUES (‘P-201′,’SOAP’,’DATTOL’,’PCS’);

The statement will be executed successfully and insert the record.

So, if you define a PRIMARY KEY constraint on two or more columns, then at least one column value of the constraint columns should be changed.

Exercise:

1) Suppose you have created a table named TRAINEE by executing the following statement:

CREATE  TABLE Trainee

(

RegistraionNo   number,

Traineename     varchar2(50),

CourseName     varchar2(50),

MobileNo          varchar2(12)

);

Now add a primary key constraint on RegistrationNo column of existing table TRAINEE and then check whether Primary Key has been set.

2) Create a table containing 5 columns and set a primary key on two columns of the table.

Check Also

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

Set NOT NULL constraint by CREATE TABLE statement in Oracle

Definition of NOT NULL constraint: The NOT NULL constraint is used to restrict null values …

One comment

  1. I appreciate your piece of work, appreciate it for all the useful articles.

Leave a Reply

Your email address will not be published. Required fields are marked *