Integrity constraints in Database

[norman]

I know constraints are rules placed to achieve the purpose and there by optimization of data in database. But what are Integrity constraints? What all commands come under this?

[jamesravid]

There are 5 integrity constrains,

1. Not Null
2. Unique
3. Primary Key
4. Foreign Key
5. Check

Not Null

A column in a table can be specified not null. It's not possible to insert a null in a NOT NULL column.

In the following create table statement col_notnull is defined as not null column.

create table Sample_table(
col_notnull number not null,
c number
);

insert into Sample_table values ( 1, null); -- legel sql statement
insert into Sample_table values ( 2, 300); -- legel sql statement
insert into Sample_table values (null, 500); -- illegal statement.

The third statement is illegal statement since col_notnull column does not accepts null values.

Unique Key

The unique constraint doesn't allow duplicate values in a column. If the unique constraint encompasses two or more columns then the combination must me unique.

The col_unique column is the unique in the following table create statement

create table Sample_table(
col_notnull number not null,
col_unique number unique,
c number
);


insert into Sample_table values ( 2, 200, 300); -- legel sql statement
insert into Sample_table values (3, 100,500); -- legel sql statement
insert into Sample_table values (4, 100,600); -- illegel sql statement

There is no problem in the first two in insert statements. But the thrid statement fails because we try to insert a duplicate value in unique field.

insert into Sample_table values ( 1, null, 100); -- legel sql statement
insert into Sample_table values ( 1, null, 100); -- legel sql statement

Null values are allowed in Unique columns. So the fourth insert statement executes successfully.

Since null <> null more than one null values are allowed in a unique field so the last insert statement is also a legel one.

Primary Key
On a technical level, a primary key combines a unique and a not null constraint. A table can have at most one primary key.


create table Sample_table(
col_notnull number not null,
col_unique number unique,
col_primarykey number primary key
c number
);



Foreign Key
A foreign key constraint (also known as referential integrity constraint) on a column ensures that the value in that column is found in the primary key (or unique key) of another table.

create table sample_child_table(
col1 varchar2(20),
col_forign_key number foreign key references sample_table(col_primary_key)
)


Check
A check constraint allows to state a minimum requirement for the value in a column. The following table allows only numbers that are between 0 and 100 in the column col_check_cons,

create table Sample_table(
col_notnull number not null,
col_unique number unique,
col_primarykey number primary key,
col_check_cons number check( col_check_cons between 0 and 100),
c number
);

insert into Sample_table values (3, 100,500,78); -- legel sql statement
insert into Sample_table values (4, 200,600,101); -- illegel sql statement

The Second insert statement fails since col_check_cons accepts values only between 0 and 100.



Hope this helps
--- James

[norman]

The detailed explanation helped me a lot for learning about the constraints. Can you tell me during alter table can we use all these constraints or is there any restrictions in the usage.

[jamesravid]

you have happily add constraints in Alter table commands.
Since NOT NULL is a column-level constraint you should use alter table...modify clause.

For others you can use either of alter table...modify clause or alter table...add clause SQL commands.

---James

[norman]

Jamesravid it was a very detailed explanation and it helped me a lot to know about integrity constraints. Thank you so much.