Constraints in Oracle Databases

Constraints are used to prevent invalid data entry into a table. We add and remove constraints after creating a table. Constraints ensure that data in a table adhere to the recognized business rules.

=>Referential integrity

Referential integrity is to establish a relationship between the data in the foreign key column in the child table with data in the primary key column in the parent table.

=>Constraint types:

1)    NOT NULL: ensures the column always contains a value.

2)    UNIQUE: ensures that a column contains unique values.

3)    PRIMARY KEY: column serves as the unique identifier for the table and is similar to a unique key, except that it cannot contain a NULL value. Table can have only one primary key.

4)    FOREIGN KEY: it establishes a relationship between the child column and parent column.

5)    CHECK: defines a condition that each row in a table must satisfy.

=>COLUMN –LEVEL CONSTRAINT:

It references a single column and is defined within the specifications of the column that it constrains.

Not null constrain is must be defined at the column level.

=>Table Level Constraint:

Defined after the definitions of the columns and references one or more columns. All constraint can be defined to a table level exclude NOT NULL constrains defined at a column level. You can disable a constraint without removing it when you create constraint is important to name the constraint

at the time you create it otherwise the server assign it a name.

=>How to Add Constraints:

By using alter table with ADD CONSTRAINT clause you can add constraint to an offered table.

For example

ALTER TABLE NEWEMP

ADD CONSTRAINT newemp_abc_pk

PRIMARY KEY (ABC);

-------------------------------------------------------------------------------------------------------------------

=>Drop Constraint

By using ALTER TABLE statement you can drop constraint, For example

ALTER TABLE NEWEMP

DROP CONSTRAINT newemp_abc_pk;

If you want drop primary key you don’t need to determine constraint name you can write

PRIMARY KEY only.

Cascade:

The optional cascade disables all dependent integrity constraints. that option applies to

PRIMARY KEY constraints.

It causes the dependent FOREIGN. For example

ALTER TABLE DEPT

Disable CONSTRAINT DEPTNO

Cascade;

If you enable the primary key, the foreign key is not automatic r-enabled you must re-enable the foreign key in 

a separate alter table statement.

--------------------------------------------------------------------------------------------------------------------------

=>How to Enable Constraint:

You can enable constraint after disabled it by ALTER TABLE statement. For example

ALTER TABLE DEPT

ENABLE CONSTRAINT DEPTNO;