Tag Archives: CREATE

Create and drop Constraint in Oracle

Leave a reply

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

ADD CONSTRAINT PRIMARY KEY ;

-- Drop the constraint
alter table Persian drop CONSTRAINT Constraint_name;

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;