Constraints

Constraints

These are the conditions or rules that we impose on any column for entering valid data into the table. Constraints are a part of the table definition that are used to limit the values entered into its columns.

Note : Constraints can be imposed in two ways

(i) Table Level :

Imposing constraints on a table level by specifying the constraint type at the end of the columns is called Table Level Constraints. In Table Level, the Constraint key word followed by Constraint name must be defined otherwise error comes.

(ii) Column Level :

Imposing constraints on a column by specifying the constraint type beside the column name is called Column Level Constraints

Constraints can be defined in two ways

1. In the Table Definition itself

2. Using Alter Command

The following are a list of constraints

1. NOT NULL CONSTRAINT
2. UNIQUE CONSTRAINT
3. PRIMARY KEY CONSTRAINT
4. CHECK CONSTRAINT
5. DEFAULT
6. REFERENCES (FOREIGN KEY CONSTRAINT )

NOT NULL

The Not Null constraint specifies that a column can not contain Nulls. To satisfy this constraint every row in the table must contain a value for the column.

if you do not specify not null, the column can contain nulls by default

Syntax:

Not Null in Column Level

Ø Create table <Table Name> ( Column <Data type><size> Not Null,

Column2 <Data Type><size> ,-------);

Adding Not Null Using Alter Command for Existing Table

Ø Alter Table <Table Name>

Modify (<Column Name > <data type> (size) not null)

Note :

1. Column must be empty to impose the not Null constraint with alter table statement.
2. Table Level is not applicable for Not Null constraint

Example :

Create table sample1(sno number(3), sname varchar2(20) not null);

Ø insert into sample1 values(&sno,'&sna');

Enter value for sno: 100

Enter value for sna: Mahesh

Enter value for sno: 101

Enter value for sna: (nothing is entered)

Error Comes

mandatory (NOT NULL) column is missing or NULL during insert

Unique Constraint

This constraint does not allow duplicate values into any column.

Syntax: (For Column Level)

Create Table <Table Name>(Column1 <Data Type><Size> Unique,

Column2 <Data Type><Size>,--------);

Syntax: (For Table Level)

Create Table <Table Name>(Column1 <Data Type><Size>,

Column2 <Data Type><Size>,--------,

Constraint <Constraint Name> Unique <Column Name>);

( Or )

Example : Imposing unique constraint Table Level

create table customer(cno number(3),cname varchar2(20),

constraint con_un unique(cno));

Ø insert into customer values(&cno,'&cname');

Enter value for cno: 100

Enter value for cname: Mahesh

Enter value for cn: 100

Enter value for c: Nithya

Error Comes

unique constraint (SCOTT.CON_UN) violated

Example : Imposing unique constraint Column Level

create table customer1(cno number(3) unique ,cname varchar2(20));

Ø insert into customer1 values(&cno, '&cname');

Enter value for cno: 100

Enter value for cname: Mahi

Enter value for cn: 100

Enter value for c: Prasad

Error Comes

unique constraint (SCOTT.SYS_C00385) violated

Default Constraint :

The default constraint is used to specify a default value for the column, when you want to given default values. You can give default value as NULL or some other value.

Note : Only Column level definition can be applied to “Default constraint”

Syntax : for column level

Create Table <Table Name>(Column1 <Data Type><Size> default <value>,

Column2 <Data Type><Size>,--------);

Example :

Ø create table sample2(sno number(3) default 10,sname varchar2(20), phone

varchar2(10) default null)

Ø insert into sample2 values(1,'Mahesh','11111')

Ø insert into sample2 values(2,'Prasad','22222')

Ø insert into sample2(sno, sname) values (3,'Nithya')

Ø insert into sample2(sno, sname) values (4,'Saloni')

Ø select * from sample2;

SNO SNAME PHONE

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

1 Mahesh 11111

2 Prasad 22222

3 Nithya

4 Saloni

Example : 2

create table sample3(sno number(3) default 100, sname varchar2(10) not null);

Ø insert into sample3 values(200,'Nithya')

Ø insert into sample3 values(201,'Aruna')

Ø insert into sample3(sname) values('Mahesh')

Ø insert into sample3(sname) values('Saloni')

select * from sample3;

SNO SNAME

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

200 Nithya

201 Aruna

100 Mahesh

100 Saloni

Check Constraint

The check constraint explicitly defines a condition. It will put restrictions on the range of the column values and the values to be accepted into the column. The condition of a check constraint can refer to any column in the table. But it can not refer to any column in other tables.

Syntax : (column Level)

Create table <table name> (<column1> <datatype> [size] check(condition),

<column2><data type> <size>,-----);

Example:

Create table items(itemno number(3) check(itemno>=100),itemname varchar2(10));

Ø Insert into items values (100,'Rice')

Ø insert into items values(101,'Paste')

Ø insert into items values(99,'Chacolate') à Error comes

Syntax : (Table Level)

Create table <table name>

(<column1> <datatype> [size],

<column2><data type> <size>,

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

constraint <constraint name> check(<column name> with condition));

Example :

create table item1(itno number(3), Itname varchar2(10),

Constraint con_check check (itno>=100));

Ø Insert into item1 values (100,'Rice')

Ø insert into item1 values(101,'Paste')

Ø insert into item1 values(99,'Chacolate') à Error comes

Primary Key Constraint

It avoids null values and also it does not allows duplicate values. Ie A Primary key is nothing but combination of not null and unique constraints

To Impose Primary Key On Any Column, You Can Use Either Table Level Or Column Level

Syntax: Table Level

create table <tablename>(column1 <datatype><size>,

column2 <datatype><size>,--------,

constraint <constraint name> primary key <column name>);

Ex:

create table items1(itemno number(3),itemname varchar2(20),

constraint con_pk primary key(itemno));

Ø insert into items1 values(100,’Rice’);

Ø insert into items1 values(100,’paste’);

Error Comes

unique constraint (SCOTT.CON_PK) violated

Syntax : Column Level

create table <tablename>(column1 <datatype><size> primary key ,

column2 <datatype><size>,--------);

Example :

create table items2(itemno number(3) primary key, itemname varchar2(20));

Ø insert into items2 values(100 ,'Wheat');

Ø insert into items2 values(100 ,'Rice');

Error Comes

unique constraint (SCOTT.CON_PK) violated

Example 2

Ø insert into item values(null ,'Wheat');

Error Comes

mandatory (NOT NULL) column is missing or NULL during insert

Foreign Key Constraint

This is used to establish relationship between 2 or more tables for inserting common or similar values into the related columns of the table.

The table from which we are establishing relation is called child table. Because the related column in the child table depends on another table for its values.

The table on which other tables are depending on the table, to which we are establishing a relation is called Mother table.

If two tables are having this relationship then the system checks whether the new value that we are inserting is related column of child table exists in the mother table or not.

There are five major restrictions related to foreign key

1. The Mother table column related must have primary key
2. We can not drop the mother table with out child table provided.
3. The data type and the size of the related columns in both the tables must be same and name can be different
4. We can establish relation to any number of tables
5. We can not impose foreign key which are having values.

Syntax : (Column Level)

Create Table <Table Name>

(<column1><data type><size>

References <Mother Table>(<column name>),

<column2><data type><size>,

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

<columnN><data type><size>);

Syntax : (Table Level)

Create Table <Table Name>

(<column1><data type><size>

<column2><data type><size>,

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

<columnN><data type><size>,

constraint <constraint Name>

foreign key(child column)

References <Mother Table> (<column name>));

Making relationships between two tables

1. First Create the Mother Table or Parent Table

create table bankmast

(accno number(3) primary key,

accna varchar2(10) not null,

balance number(8,2));

2. Next Create the Child Table

create table banktrans

(acno number(3) references bankmast(accno),

trmode char(1),trdate date,

amount number(8,2));

3. Next insert the values in to mother table first,

Ø insert into bankmast values(&accno,'&accna',&bal);

select * from bankmast;

ACCNO ACCNA BALANCE

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

100 Priya 5000

101 Mahesh 6500

102 Saryu 4500

103 Nandhini 7600

4. Next insert values into child table

Ø insert into banktrans values(&acno,'&trmode','&trdate',&amt);

Ø insert into banktrans values(100,'D','5-May-05',5000);

Ø insert into banktrans values(102,'W','15-Apr-05',2000);

Ø insert into banktrans values(104,'D','10-Feb-05',10000);

ERROR : (since there is no such account number present in the mother table)

ORA-02291: integrity constraint (SCOTT.SYS_C00674) violated - parent key not found

SQL> select * from banktrans;

ACNO T TRDATE AMOUNT

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

100 d 05-MAY-05 5000

102 w 01-JUN-05 2000

Dropping Relationship Tables

Note :

1. if you try to drop the mother table when child table is present, it will give an error message

drop table bankmast;

ERROR :

ORA-02449: unique/primary keys in table referenced by foreign keys

1. To drop the mother table, First Drop the Child table and then drop the mother table

Drop table banktrans;

Drop table bankmast;