Primary and foreign Key Altering Issue /How to rename constraint

We can not create/alter same constraint on same column in table if the same constraint is all ready present in table for same column.

Note:Here I am talking about constraint not about key.

Primary Key Example :

Step 1:Create supplier table

CREATE TABLE supplier

(

supplier_id numeric(10) not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50),

CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name) );

Step 2:Check table is created or not?

select * from supplier

Step 3:Try to alter primary key constraint with different name .It will throw error.

ALTER TABLE supplier

ADD CONSTRAINT supplier_pk1 PRIMARY KEY (supplier_id, supplier_name);

We can conclude that we can not alter or add constraint if it is present in table for same column.Table can have only one primary key and primary constraint in table.

Foreign Key Example :

Step 1:Create supplier table

CREATE TABLE supplier

(

supplier_id numeric(10) not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50),

CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name) );

Step 2:Check table is created or not?

select * from supplier

Step 3:Create second table .

CREATE TABLE Orders

(

supplier_id numeric(10) not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50)

);

Step 4:Check table is created or not?

select * from Orders

Step 5:Add constraint to orders tables

ALTER TABLE Orders

ADD CONSTRAINT fk_supplier

FOREIGN KEY (supplier_id, supplier_name)

REFERENCES supplier(supplier_id, supplier_name);

Step 5:Check table constraints

desc Orders

Step 6:Add another constraint with another name  to orders tables

ALTER TABLE Orders

ADD CONSTRAINT fk_supplier1

FOREIGN KEY (supplier_id, supplier_name)

REFERENCES supplier(supplier_id, supplier_name);

This will throw an error.

We can conclude that we can not alter or add constraint if it is present in table for same column.In table orders fk_supplier constraint is allready present so if we try to add another constraint with name fk_supplier1. It wont allow you.

Solution for above issues:

For resolving this issue you have to use rename query.

ALTER TABLE Orders RENAME CONSTRAINT fk_supplier TO fk_supplier1

This query will allow you to rename constraint with new name.

Note:If you want to check constraint in table you can use following query.

Select * from all_constraints where table_name ='ORDERS'