Add & Drop primary or unique key to a table in Oracle

Alter Constraint to a Table in Oracle

Add Primary key without naming convention

ALTER TABLE employees ADD PRIMARY KEY (employee_id);

Add primary key to the table with naming convention

ALTER TABLE employee ADD CONSTRAINT pk_empid PRIMARY KEY (emp_id);

Add unique key in table

SQL> ALTER TABLE hr.employees ADD CONSTRAINT emp_email_uk UNIQUE(email);
Table altered.

Drop the Primary Key constraint

ALTER TABLE hr.employees DROP PRIMARY KEY;

ALTER TABLE hr.employees DROP CONSTRAINT pk_empid;

Drop the unique key constraint

SQL> alter table hr.employees drop constraint emp_email_uk;
Table altered.

SQL> select constraint_name,owner,constraint_type,table_name,index_owner,index_name from dba_constra
ints where owner='HR' and table_name='EMPLOYEES' and index_name is not null;

CONSTRAINT_NAME      OWNER  C TABLE_NAME           INDEX_ INDEX_NAME
-------------------- ------ - -------------------- ------ --------------------
EMP_EMP_ID_PK        HR     P EMPLOYEES            HR     EMP_EMP_ID_PK

Note: Error received if you tried to create one more primary key on one table. One table has only one primary key.
ORA-02260: table can have only one primary key

SQL> ALTER TABLE hr.employees ADD PRIMARY KEY (employee_id);
ALTER TABLE hr.employees ADD PRIMARY KEY (employee_id)
*
ERROR at line 1:
ORA-02260: table can have only one primary key

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.