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
