Monday, August 6, 2012

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 APrimary 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;

No comments:

Post a Comment

Thank you :
- kareem