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
-- Create the Primary key constraint
ALTER TABLE
FOREIGN KEY
-- Create the FOREIGN KEY Constraint
ALTER TABLE Department ADD FOREIGN KEY (DEPT_ID) REFERENCES employees(ID);
ALTER TABLE Department ADD CONSTRAINT FK_EmployeeDept FOREIGN KEY (DEPT_ID) REFERENCES employees(ID);
-- Drop the FOREIGN KEY constraint
ALTER TABLE Orders DROP CONSTRAINT FK_EmployeeDept;
CHECK
-- Add the check constraint
ALTER TABLE Employees ADD CHECK (Exp >= 5);
ALTER TABLE Employees ADD CONSTRAINT CHK_Experience CHECK ( Exp >= 5);
-- Drop the check constraint
ALTER TABLE Employees DROP CONSTRAINT CHK_Experience;
DEFAULT
--Create the Default constraint
ALTER TABLE Employees MODIFY City DEFAULT 'NA';
-- Drop the default constraint
ALTER TABLE Employees ALTER COLUMN City DROP DEFAULT;