Create and drop Constraint in Oracle

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;

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.