Constraints | Oracle DBA Tutorial pdf

Constraints

Types of constraints – not null, check, unique, primary key, foreign key
ALTER TABLE table MODIFY column NOT NULL
ALTER TABLE table MODIFY column NULL
ALTER/CREATE TABLE table (….)
CONSTRAINT ck_bonus check ( bonus > 0 )
ALTER TABLE table ADD CONSTRAINT ck_bonus check ( bonus > 0 )
CREATE TABLE product (….)
CONSTRAINT pk_prodcode PRIMARY KEY (prodcode)
ALTER TABLE product ADD CONSTRAINT pk_prodcode PRIMARY KEY (prodcode)
ALTER TABLE product ADD CONSTRAINT fk_mfd FOREIGN KEY
(mfdby) REFERENCES manufacturer(mfdno) ON DELETE CASCADE
Constraints created are enabled by default. You can create it as disabled by
using DISABLED keyword at end of command.
ALTER TABLE table DROP CONSTRAINT constraint
ALTER TABLE table DROP PRIMARY KEY CASCADE

 Enabling/disabling constraints

ALTER TABLE table DISABLE CONSTRAINT constraint
ALTER TABLE table ENABLE CONSTRAINT constraint
ALTER TABLE table MODIFY CONSTRAINT constraint ENABLE
ALTER TABLE table MODIFY CONSTRAINT constraint DISABLE

Validated constraints

Enable validate – default, existing rows and future rows are checked
Enable novalidate – existing rows not checked but future rows are checked
Disable validate – existing rows checked but future rows are not checked (no DML is allowed on table)
Disable novalidate – no check done on existing or future rows
ALTER TABLE table MODIFY CONSTRAINT constraint ENABLE NOVALIDATE

Deferring constraints

If constraint is created with DEFERABLE clause, you can define whether constraint checking will be done immediately (INITIALLY IMMEDIATE, default) or later (INITIALLY DEFEREED).
ALTER TABLE name MODIFY CONSTRAINT cons_name INITALLY DEFFERED (or IMMEDIATE)

Index related data dictionary views

DBA_CONSTRAINTS
DBA_CONS_COLUMNS