Create and drop Constraint in Oracle
Note: In case of primary/unique constraint, you delete constraint then its index is also deleted if both have same name.
Check the name of constraint and constraint type
col owner for a10
col constraint_name for a10
col constraint_type for a10
Select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE from DBA_CONSTRAINTS where table_name='K';
OWNER CONSTRAINT CONSTRAINT
---------- ---------- ----------
SYS TBL_PK P
Constraint Type:
C (check constraint a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (read-only on view)
PRIMARY KEY -- Drop the constraint FOREIGN KEY ALTER TABLE Department ADD CONSTRAINT FK_EmployeeDept FOREIGN KEY (DEPT_ID) REFERENCES employees(ID); -- Drop the FOREIGN KEY constraint CHECK ALTER TABLE Employees ADD CONSTRAINT CHK_Experience CHECK ( Exp >= 5); -- Drop the check constraint DEFAULT -- Drop the default constraint
-- Create the Primary key constraint
ALTER TABLE ADD CONSTRAINT PRIMARY KEY ;
alter table Persian drop CONSTRAINT ;
-- Create the FOREIGN KEY Constraint
ALTER TABLE Department ADD FOREIGN KEY (DEPT_ID) REFERENCES employees(ID);
ALTER TABLE Orders DROP CONSTRAINT FK_EmployeeDept;
-- Add the check constraint
ALTER TABLE Employees ADD CHECK (Exp >= 5);
ALTER TABLE Employees DROP CONSTRAINT CHK_Experience;
--Create the Default constraint
ALTER TABLE Employees MODIFY City DEFAULT 'NA';
ALTER TABLE Employees ALTER COLUMN City DROP DEFAULT;
