Constraints

Constraints:

Constraints are useful for preventing the invalid data and also for data security. Constraints are as follows…

• Primary Key Constraint.
• Foreign key ( aka. reference key)
• Not NULL
• Unique
• Check

Not NULL:

As name says, this constraint will not allow the column as empty column. If any column define as NOT NULL constraint that column should contain the data. If we try to insert NULL. It will throw an error that states “Integrity constraint error: NOT NULL constraint violated”.

We can add this constraint my modifying the table (alter table modify….) but, that column should not have null values.

Note: Space is NOT a null value.

Unique:

Unique constraint will not allow duplicate value in the column. But, it will allow null values. (‘N’ number of null values).

We can add this constraint my modifying the table (alter table modify….) but, that column should not have duplicate values.

Check

While inserting the record, using check constraint we can check the data before insert. It will easy to understand in example.

 

Primary key

Primary key is a combination of NOT NULL and UNIQUE constraint key. This also called as parent key. The column defined as PRIMARY will not allow NULL values and also duplicate values.

We can add this constraint my modifying the table (alter table modify….) but, that column should not have null and duplicate values.

 

We can’t drop the primary column or table contains primary column before disable or drop foreign key constraint column or table contains foreign key defined table.

If we tried to drop the table or column we will get the error.

 

Foreign key

Foreign key is same like as unique constraint. It will not allow the duplicate values but, will allow the null values. This also called as a reference key. The data which are in the reference key column should have the same value as PRIMARY key. We can’t insert the values which are not in primary column.

We can add this constraint my modifying the table (alter table modify….) but, that column should not have duplicate values and also data should be available in primary key.

Characteristics:

Any time we can “enable” or “disable” the constraints but, whenever we are doing that, all criteria should be valid for the constraints.

Example for creating table:

Create table Arun (Id number (10) constraint pk_id Primary key,

Name Varchar2 (32) constraint NOT NULL,

age number (2) constraint age_check check (age>18) ,

contact_number number (12) constraint unique);

Create table Vars (Id number(2) constraint fk_id references arun(id));