Google Search

Wednesday, October 27, 2010

Integrity Constraints - To study the various constraints available in the SQL query language.

DOMAIN INTEGRITY CONSTRAINTS

NOT NULL CONSTRAINT
SQL> create table empl (ename varchar2(30) not null, eid varchar2(20) not null);
Table created.

SQL> insert into empl values ('abcde',11);
1 row created.

SQL> insert into empl values ('fghij',12);
1 row created.

SQL> insert into empl values ('klmno',null);
insert into empl values ('klmno',null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ITA"."EMPL"."EID")

SQL> select * from empl;
ENAME                           EID
------------------------------ --------------------
abcde                               11
fghij                                 12

CHECK AS A COLUMN CONSTRAINT
SQL> create table depts ( dname varchar2(30) not null, did number(20) not null check (did<10000));
Table created.

SQL> insert into depts values ('sales ',9876);
1 row created.

SQL> insert into depts values ('marketing',5432);
1 row created.

SQL> insert into depts values ('accounts',789645);
insert into depts values ('accounts',789645)
*
ERROR at line 1:
ORA-02290: check constraint (ITA.SYS_C003179) violated
SQL> select * from depts;
DNAME                                DID
------------------------------ ---------
sales                                   9876
marketing                           5432

CHECK AS A TABLE CONSTRAINT
SQL> create table airports (aname varchar2(30) not null , aid number(20) not null, acity varchar2(30) check( acity in ('chennai','hyderabad','bangalore')));
Table created.

SQL> insert into airports values( 'abcde', 100,'chennai');
1 row created.

SQL> insert into airports values( 'fghij', 101,'hyderabad');
1 row created.

SQL> insert into airports values( 'klmno', 102,'bangalore');
1 row created.

SQL> insert into airports values( 'pqrst', 103,'mumbai');
insert into airports values( 'pqrst', 103,'mumbai')
*
ERROR at line 1:
ORA-02290: check constraint (ITA.SYS_C003187) violated

SQL> select * from airports;
ANAME                                AID ACITY
------------------------------ --------- ------------------------------
abcde                                  100      chennai
fghij                                   101       hyderabad
klmno                                102       bangalore

ENTITY INTEGRITY CONSTRAINTS

UNIQUE AS A COLUMN CONSTRAINT

SQL> create table book (bname varchar2(30) not null, bid number(20) not null unique);
Table created.

SQL> insert into book values ('fairy tales',1000);
1 row created.

SQL> insert into book values ('bedtime stories',1001);
1 row created.

SQL> insert into book values ('comics',1001);
insert into book values ('comics',1001)
*
ERROR at line 1:
ORA-00001: unique constraint (ITA.SYS_C003130) violated

SQL> select * from book;
BNAME                                BID
------------------------------ ---------
fairy tales                             1000
bedtime stories                     1001

UNIQUE AS A TABLE CONSTRAINT

SQL> create table orders( oname varchar2(30) not null , oid number(20) not null , unique(oname,oid));
Table created.

SQL> insert into orders values ('chair', 2005);
1 row created.

SQL> insert into orders values ('table',2006);
1 row created.

SQL> insert into orders values ('chair',2007);
1 row created.

SQL> insert into orders values ('chair', 2005);
insert into orders values ('chair', 2005)
*
ERROR at line 1:
ORA-00001: unique constraint (ITA.SYS_C003152) violated
SQL> select * from orders;

ONAME                                OID
------------------------------ ---------
chair                               2005
table                               2006
chair                               2007

PRIMARY KEY AS A COLUMN CONSTRAINT

SQL> create table custo ( cname varchar2(30) not null , cid number(20) not null primary key);
Table created.

SQL> insert into custo values ( 'jones', 506);
1 row created.

SQL> insert into custo values ('hayden',508);
1 row created.

SQL> insert into custo values ('ricky',506);
insert into custo values ('ricky',506)
*
ERROR at line 1:
ORA-00001: unique constraint (ITA.SYS_C003165) violated

SQL> select * from custo;
CNAME                                CID
------------------------------ ---------
jones                                  506
hayden                               508

PRIMARY KEY AS A TABLE CONSTRAINT

SQL> create table branches( bname varchar2(30) not null , bid number(20) not null , primary key(bnam e,bid));
Table created.

SQL> insert into branches values ('anna nagar', 1005);
1 row created.

SQL> insert into branches values ('adyar',1006);
1 row created.

SQL> insert into branches values ('anna nagar',1007);
1 row created.
SQL> insert into branches values ('anna nagar', 1005);
insert into branches values ('anna nagar', 1005)
*
ERROR at line 1:
ORA-00001: unique constraint (ITA.SYS_C003173) violated

SQL> select * from branches;

BNAME                                BID
------------------------------ ---------
anna nagar                          1005
adyar                                  1006
anna nagar                          1007

REFERENTIAL INTEGRITY CONSTRAINTS

TO CREATE ‘DEPTS’ TABLE
SQL> create table depts(city varchar2(20), dno number(5) primary key);
Table created.
SQL> insert into depts values('chennai', 11);
1 row created.
SQL> insert into depts values('hyderabad', 22);
1 row created.

TO CREATE ‘SEMP’ TABLE
SQL> create table semp(ename varchar2(20), dno number(5) references depts(dno));
Table created.
SQL> insert into semp values('x', 11);
1 row created.
SQL> insert into semp values('y', 22);
1 row created.
SQL> select * from semp;
ENAME                      DNO
-------------------- ---------
x                           11
y                           22

ALTER TABLE
SQL> alter table semp add(eddress varchar2(20));
Table altered.
SQL> update semp set eddress='10 gandhi road' where dno=11;
1 row updated.
SQL> update semp set eddress='12 m.g. road' where dno=22;
1 row updated.

SQL> select * from semp;
ENAME                     DNO    EDDRESS
-------------------- --------- --------------------
x                              11            10 gandhi road
y                              22            12 m.g. road

SQL> select city, ename from depts, s2emp where depts.dno = s2emp.dno;
CITY                 ENAME
-------------------- --------------------
chennai              x
hyderabad            y

0 comments:

Post a Comment