Constraints

Oracle uses constraints to prevent invalid data entry into a table.

You can add and remove constraint after creating a table.

Constraints ensure that data in a table adhere to the recognized business rules.

Referential integrity

In the foreign key 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 constrain 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.

Add Constraints

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

Not : before add constraint view

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;