Integrity constraints in Database

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?

There are 5 integrity constrains,

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

[B][SIZE="3"][SIZE="2"]Not Null[/SIZE][/SIZE][/B]

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.

[B]create table Sample_table(
col_notnull number [COLOR="Blue"]not null[/COLOR],
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.[/B]

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

[B][SIZE="2"]Unique Key[/SIZE][/B]

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

[B]create table Sample_table(
col_notnull number not null,
col_unique number [COLOR="Blue"]unique[/COLOR],
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
[/B]
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.

[B]insert into Sample_table values ( 1, null, 100); -- legel sql statement
insert into Sample_table values ( 1, null, 100); -- legel sql statement[/B]

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.

[B][SIZE="2"]Primary Key[/SIZE][/B]
On a technical level, a primary key combines a unique and a not null constraint. A table can have at most one primary key.


[B]create table Sample_table(
col_notnull number not null,
col_unique number unique,
col_primarykey number [COLOR="Blue"]primary key[/COLOR]
c number
);[/B]



[B][SIZE="2"]Foreign Key[/SIZE][/B]
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.

[B]create table sample_child_table(
col1 varchar2(20),
col_forign_key number [COLOR="Blue"]foreign key references sample_table(col_primary_key)[/COLOR]
)
[/B]

[B][SIZE="2"]Check[/SIZE][/B]
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,

[B]create table Sample_table(
col_notnull number not null,
col_unique number unique,
col_primarykey number primary key,
col_check_cons number [COLOR="Blue"]check( col_check_cons between 0 and 100)[/COLOR],
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.
[/B]


Hope this helps :)
--- James

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.

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

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

---James

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