EX.No: 3   CONSTRAINTS

AIM

To work on various constraints in DBMS

CONSTRAINTS

Constraints are part of the table definition that limits and restriction on the value

entered into its columns.

TYPES OF CONSTRAINTS:

1) Primary key

2) Foreign key/references

3) Check

4) Unique

5) Not null

6) Null

7) Default

CONSTRAINTS CAN BE CREATED IN THREE WAYS:

1) Column level constraints

2) Table level constraints

3) Using DDL statements-alter table command

OPERATION ON CONSTRAINT:

i) ENABLE

ii) DISABLE

iii) DROP

ALGORITHM

STEP 1: Start

STEP 2: Create the table with its essential attributes.

STEP 3: Execute Commands with different constraints and extract information from the table.

STEP 4: Stop

QUERIES
CONSTRAINTS
PRIMARY KEY CONSTRAINTS:

SQL>create table customer(cusno number(5) primary key, custname varchar(10), salary number(6,2),city varchar(8));

SQL>  insert into customer values(&cusno,’&custname’,&cussal,’&cuscity’);

Enter value for cusno: 1

Enter value for custname: a

Enter value for cussal: 5000

Enter value for cuscity: chennai

old   1:  insert into customer values(&cusno,’&custname’,&cussal,’&cuscity’)

new   1:  insert into customer values(1,’a’,5000,’chennai’)

1 row created.

SQL> /

Enter value for cusno: 2

Enter value for custname: b

Enter value for cussal: 6000

Enter value for cuscity: mumbai

old   1:  insert into customer values(&cusno,’&custname’,&cussal,’&cuscity’)

new   1:  insert into customer values(2,’b’,6000,’mumbai’)

1 row created.

SQL> /

Enter value for cusno: 3

Enter value for custname: c

Enter value for cussal: 7000

Enter value for cuscity: delhi

old   1:  insert into customer values(&cusno,’&custname’,&cussal,’&cuscity’)

new   1:  insert into customer values(3,’c’,7000,’delhi’)

1 row created.

SQL> /

Enter value for cusno: 3

Enter value for custname: g

Enter value for cussal: 8000

Enter value for cuscity: chennai

old   1:  insert into customer values(&cusno,’&custname’,&cussal,’&cuscity’)

new   1:  insert into customer values(3,’g’,8000,’chennai’)

 insert into customer values(3,’g’,8000,’chennai’)

ERROR at line 1:

ORA-00001: unique constraint (KUMARM.SYS_C008128) violated

SQL> select *from customer;

CUSNO       CUSTNAME       SALARY      CITY

———-         ———-         ———-            ——–

    1                 a                   5000               chennai

    2                 b                   6000               mumbai

    3                 c                   7000               delhi

UNIQUE  KEY CONSTRAINTS

SQL> create table customeruniq(cusno number(4) unique, cusname varchar(6));

Table created.

SQL> insert into customeruniq values(&no, ‘&cusname’);

Enter value for no: 1

Enter value for cusname: a

old   1: insert into customeruniq values(&no, ‘&cusname’)

new   1: insert into customeruniq values(1, ‘a’)

1 row created.

SQL> /

Enter value for no: 2

Enter value for cusname: b

old   1: insert into customeruniq values(&no, ‘&cusname’)

new   1: insert into customeruniq values(2, ‘b’)

1 row created.

SQL> /

Enter value for no: null

Enter value for cusname: c

old   1: insert into customeruniq values(&no, ‘&cusname’)

new   1: insert into customeruniq values(null, ‘c’)

1 row created.

SQL> /

Enter value for no: 2

Enter value for cusname: k

old   1: insert into customeruniq values(&no, ‘&cusname’)

new   1: insert into customeruniq values(2, ‘k’)

insert into customeruniq values(2, ‘k’)

*ERROR at line 1:

ORA-00001: unique constraint (KUMARM.SYS_C008133) violated

SQL> SELECT *FROM  customeruniq;

CUSNO            CUSNAM

———             ———

         1                        a

  2                        b

                     c

CHECK CONSTRAINTS:

SQL> create table customercheck(cusno number(5) ,cusname varchar(4), sal number(5) , check(sal>5000));

Table created.

SQL> insert into customercheck values(&cusno,’&custname’,&cussal);

Enter value for cusno: 2

Enter value for custname: a

Enter value for cussal: 6000

old   1:  insert into customercheck values(&cusno,’&custname’,&cussal)

new   1:  insert into customercheck values(2,’a’,6000)

1 row created.

SQL> /

Enter value for cusno: 1

Enter value for custname: b

Enter value for cussal: 8000

old   1:  insert into customercheck values(&cusno,’&custname’,&cussal)

new   1:  insert into customercheck values(1,’b’,8000)

1 row created.

SQL> /

Enter value for cusno: 3

Enter value for custname: c

Enter value for cussal: 3000

old   1:  insert into customercheck values(&cusno,’&custname’,&cussal)

new   1:  insert into customercheck values(3,’c’,3000)

 insert into customercheck values(3,’c’,3000)

*

ERROR at line 1:

ORA-02290: check constraint (KUMARM.SYS_C008129) violated

Enter value for cusno: 1

Enter value for custname: a

Enter value for cussal: 4000

old   1:  insert into customercheck values(&cusno,’&custname’,&cussal)

new   1:  insert into customercheck values(1,’a’,4000)

 insert into customercheck values(1,’a’,4000)

*

ERROR at line 1:

ORA-02290: check constraint (KUMARM.SYS_C008129) violated

SQL> select *from customercheck;

CUSNO       CUSN                    SAL

———         ——-                ———-

    2                a                       6000

           1                b                       8000

NULL AND NOT NULL CONSTRIANTS:

create table customernull (cusno number(5), cusname varchar(5),cusal number(5) not null, cusph number(10)  null)

SQL> /

Enter value for cusno: 1

Enter value for cusname: a

Enter value for cusal: 4000

Enter value for cusph: 123456

old   1: insert into customernull values(&cusno,’&cusname’ ,&cusal,&cusph)

new   1: insert into customernull values(1,’a’ ,4000,123456)

1 row created.

SQL> /

Enter value for cusno: 2

Enter value for cusname: b

Enter value for cusal: 6000

Enter value for cusph: null

old   1: insert into customernull values(&cusno,’&cusname’ ,&cusal,&cusph)

new   1: insert into customernull values(2,’b’ ,6000,null)

1 row created.

SQL> /

Enter value for cusno: 3

Enter value for cusname: c

Enter value for cusal: null

Enter value for cusph: null

old   1: insert into customernull values(&cusno,’&cusname’ ,&cusal,&cusph)

new   1: insert into customernull values(3,’c’ ,null,null)

insert into customernull values(3,’c’ ,null,null)

ERROR at line 1:

ORA-01400: cannot insert NULL into (“KUMARM”.”CUSTOMERNULL”.”CUSAL”)

SQL> select *from customernull;

CUSNO       CUSNA      CUSAL      CUSPH

———-        ——–       ——–        ———-

1             a             4000          12346

2             b             6000

FOREIGN KEY CONSTRIANTS:

SQL> create table parent2( regno number(5) primary key, name varchar(5), mark number(4));

Table created.

SQL> create table child2 ( regno number(5) references parent2(regno), city varchar(5));

Table created.

SQL> insert into parent2 values(&regno,’&name’,&mark);

Enter value for regno: 1

Enter value for name: a

Enter value for mark: 100

old   1: insert into parent2 values(&regno,’&name’,&mark)

new   1: insert into parent2 values(1,’a’,100)

1 row created.

SQL> /

Enter value for regno: 2

Enter value for name: b

Enter value for mark: 60

old   1: insert into parent2 values(&regno,’&name’,&mark)

new   1: insert into parent2 values(2,’b’,60)

1 row created.

SQL> /

Enter value for regno: 3

Enter value for name: c

Enter value for mark: 78

old   1: insert into parent2 values(&regno,’&name’,&mark)

new   1: insert into parent2 values(3,’c’,78)

1 row created.

SQL> select *from  parent2 ;

REGNO                   NAME                MARK

———                    ——–                ———-

   1                              a                      100

   2                              b                       60

   3                              c                        78

   4                              d                        45

   5                              e                        56

SQL> insert into child2 values(&regno,’&city’);

SQL> /

Enter value for regno: 1

Enter value for city: chn

old   1: insert into child2 values(&regno,’&city’)

new   1: insert into child2 values(1,’chn’)

1 row created.

SQL> /

Enter value for regno: 2

Enter value for city: mum

old   1: insert into child2 values(&regno,’&city’)

new   1: insert into child2 values(2,’mum’)

1 row created.

SQL> /

Enter value for regno: 3

Enter value for city: del;

old   1: insert into child2 values(&regno,’&city’)

new   1: insert into child2 values(3,’del;’)

1 row created.

SQL> /

Enter value for regno: 7

Enter value for city: chn

old   1: insert into child2 values(&regno,’&city’)

new   1: insert into child2 values(7,’chn’)

insert into child2 values(7,’chn’)

ERROR at line 1:

ORA-02291: integrity constraint (KUMARM.SYS_C008135) violated – parent key not found

CONSTRAINTS:

create table constall ( regno number(5) primary key, name varchar(5) not null, mark number(5) check(mark<=100),phno number(10) unique)

Table created.

RESULT

Thus various constraints were created for various tables and output was verified.

Leave a Reply

Your email address will not be published. Required fields are marked *