What the Best SQL Constraints Pros Do (and You Should Too)

Constraints: the following constraints are available in Oracle SQL.

1. not null
2. primary key
3. foreign key
4. check
5. unique

Not Null Constraint

NULL is not a value ,it is the meaning that value is not provided in this column.

NULL is not Zero or space or Empty string, it is just a unspecified value.

when you apply NOT NULL constraint in column then DBMS system Doesn't allow

NULL value in this column.

create table student1(
sid number(10) not null,
sname char(20),
fee number(6,2),
phone number(10)not null);+
 
SQL> create table student1(
  2  sid number(10) not null,
  3  sname char(20),
  4  fee number(6,2),
  5  phone number(10)not null);
Table created.

SQL> desc student1;
       SID         SNAME           FEE      PHONE
---------- --------------------    ---------- ----------
       111           tara              1000.99        777
       333           tarun             3000.99        555
       555           tanuja           5000.99        999
       111           rqmqn          1000.96        777
       777           rqmqn                               777

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 SID                                          NOT NULL NUMBER(10)
 SNAME                                                       CHAR(20)
 FEE                                                              NUMBER(6,2)
 PHONE                                    NOT NULL NUMBER(10)

insert into student1 values(111,'tara',1000.99,777);
insert into student1 values(333,'tarun',3000.99,555);
insert into student1 values(555,'tanuja',5000.99,999);

 SQL> select * from student1;

       SID          SNAME     FEE      PHONE

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

       111           tara             1000.99        777
       333           tarun            3000.99        555
       555           tanuja          5000.99        999

 insert into student1 values(111,'rqmqn',1000.96,777);

SQL> select * from student1;

       SID          SNAME     FEE      PHONE
---------- -------------------- ---------- ----------
       111           tara          1000.99        777
       333           tarun        3000.99        555
       555           tanuja       5000.99        999
       111           rqmqn       1000.96        777

 insert into student1(sname,phone) values('rqmqn',777);

 SQL> insert into student1(sname,phone) values('rqmqn',777);

insert into student1(sname,phone) values('rqmqn',777)

*ERROR at line 1:

ORA-01400: cannot insert NULL into ("SCOTT"."STUDENT1"."SID")

insert into student1(sid,sname,phone) values(777,'rqmqn',777);

SQL> insert into student1(sid,sname,phone) values(777,'rqmqn',777);

1 row created.

SQL> select * from student1;

insert into student1(sid,sname,feebal) values(777,'rqmqn',777.34);

SQL> insert into student1(sid,sname,feebal) values(777,'rqmqn',777.34);

insert into student1(sid,sname,feebal) values(777,'rqmqn',777.34)

*ERROR at line 1:

ORA-00904: invalid column name

insert into student1(sid,phone) values(987,369);

SQL> insert into student1(sid,phone) values(987,369);

 1 row created.

 SQL> select * from student1;

       SID          SNAME     FEE      PHONE

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

       111           tara           1000.99        777
       333           tarun         3000.99        555
       555           tanuja        5000.99        999
       111           rqmqn       1000.96        777
       777           rqmqn                             777

6 rows selected.

Unique Constraint:

when you apply unique constraint for a column then DBMS system doesn't allow duplicate value in this column.
when a column is specified with a unique constraint then we can have multiple null value.

 
create table student2(sid number(10) unique,
sname char(15)not null,
phone number(10));
 

SQL> desc student2;

 Name                                      Null?    Type

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

 SID                                            NOT NULL NUMBER(10)

 SNAME                                     NOT NULL CHAR(15)

 PHONE                                                           NUMBER(10)

insert into student2 values(123,'rajiv',9887);

insert into student2 values(345,'raman',9456);

insert into student2 values(567,'soniya',9987);

SQL> select * from student2; 

       SID          SNAME           PHONE

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

       123           rajiv                 9887

       345           raman                9456

       567           soniya               9987

insert into student2 values(123,'gopi',9227); 

SQL> insert into student2 values(123,'gopi',9227);

insert into student2 values(123,'gopi',9227)

*ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C002725) violated

insert into student2 values(121,'rajiv',9887);

SQL> insert into student2 values(121,'rajiv',9887);
 1 row created.
insert into student2(sid,sname) values(129,'raju');

SQL> insert into student2(sid,sname) values(129,'raju');

 1 row created.

insert into student2(sname,phone) values('rani',9987);

here we can insert the value in  table student2 if we cann't consider

NOT NULL value of SID at time of table creation....student2.

SQL> select* from student2; 

       SID          SNAME         PHONE

---------- --------------               ----------
       123           rajiv                 9887
       345           raman              9456
       567           soniya             9987
       121           rajiv                9887
       129           raju
                        rani                9987

6 rows selected.

Primary Constraint

when you specified column as primary key it enforces two constraints NOT NULL & UNIQUE constraints.
we can specify only one primary key in a table.

two types of primary key
simple primary key
composite primary key

when primary key is defined for a single column of a table ,then it is called as simple primary key.
when primary key is defined for a multiple column of a table ,
then it is called as composite primary key.
 

create table student3(

sid number(10) primary key,

sname char(20),

sal number(6,2),

phone number(10)not null);

SQL> desc student3;

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 SID                                       NOT NULL NUMBER(10)
 SNAME                                                   CHAR(20)
 SAL                                                          NUMBER(6,2)
 PHONE                                NOT NULL NUMBER(10)
insert into student3 values(111,'a',9000.99,777);
insert into student3 values(333,'t',8000.99,555);
insert into student3 values(555,'x',7000.99,999);

SQL> select * from student3; 

       SID          SNAME               SAL       PHONE
---------- --------------------        ----------      ----------
       111            a                      9000.99        777
       333           t                       8000.99        555
       555           x                      7000.99        999

 

insert into student3 values(333,'P',9677.99,789);

SQL> insert into student3 values(333,'P',9677.99,789);

insert into student3 values(333,'P',9677.99,789)

*ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C002729) violated

insert into student3(sname,sal,phone) values('P',9677.99,789);

SQL> insert into student3(sname,sal,phone) values('P',9677.99,789);

insert into student3(sname,sal,phone) values('P',9677.99,789)

*ERROR at line 1:

ORA-01400: cannot insert NULL into ("SCOTT"."STUDENT3"."SID")

Check Constraint

 It is used to verify some condition on specified column.

when you inserting a record in customer table then dbms system verify whether provided value for the
city is satisfy the given condition or not.

create table cust(cid number(19)primary key,cname char(20)not null,email char(30)unique,phone
number(20)unique,dob date not null,city char(30)check( city in('bangalore','hydrabad')));

create table cust1(cid number(5)primary key,cname char(2)not null,email char(17)unique,phone

number(8)unique,dob date not null,

city char(10)check( city ='bangalore'or city='hydrabad'or city='mumbai'));

SQL> desc cust1;

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 CID                                       NOT NULL NUMBER(19)
 CNAME                                NOT NULL CHAR(20)
 EMAIL                                                      CHAR(30)
 PHONE                                                      NUMBER(20)
 DOB                                      NOT NULL DATE
 CITY                                                          CHAR(30)

 
insert into cust1 values(111,'X','XA@gmail.com',1234,'08-jan-1980','bangalore');
insert into cust1 values(222,'Y','YA@gmail.com',7864,'05-jun-1984','mumbai');
insert into cust1 values(333,'p','pg@gmail.com',1999,'06-may-1980','bangalore');
insert into cust1 values(244,'K','go@gmail.com',7888,'17-july-1989','hydrabad');

SQL>      select * from cust1;

 

       CID   CN                   EMAIL                PHONE        DOB       CITY
---------- -- -----------------                  ----------      ---------       ----------
       111      X                 XA@gmail.com     1234      08-JAN-80    bangalore
       222      Y                 YA@gmail.com     7864      05-JUN-84    mumbai
       333      p       pg@gmail.com      1999      06-MAY-80  bangalore
       244     K     go@gmail.com       7888      17-JUL-89    hydrabad

insert into cust1 values(777,'M','UT@gmail.com',7333,'13-july-1984','patna');

SQL> insert into cust1 values(777,'M','UT@gmail.com',7333,'13-july-1984','patna');

insert into cust1 values(777,'M','UT@gmail.com',7333,'13-july-1984','patna')

*ERROR at line 1:

ORA-02290: check constraint (SCOTT.SYS_C002761) violated

insert into cust1 values(777,'M','UT@gmail.com',7333,'13-july-1984','bangalore');

SQL>insert into cust1 values(777,'M','UT@gmail.com',7333,'13-july-1984','bangalore');

 1 row created.

Foreign Key Constraint

it is used to establish the relationship b/w tables.
foreign key is nothing but the key which is referred with respect to the
primary key of some other table.

 

when primary key is defined for two or more than two column then it is called

as composite primary key.

create table customer(cid number(4) primary key , cname char(15),email_id char(15

),DOB date,phone_no number(7),status char(10));

insert into customer values(1001,'rakesh kumar','ru@gmail.com','26-may-1967',313122,'ACTIVE');
insert into customer values(1002,'rajiv shyam','ra@gmail.com','17-jun-1987',2223453,'ACTIVE');
insert into customer values(1003,'sunil jha','sun@yahoo.com','12-feb-1955',1243565,'INACTIVE');

insert into customer values(1111,'raman shyam','ram@gmail.com','19-mar-2003',1113422,'ACTIVE');

insert into customer values(1099,'vijay goal','vi@rediff.com','17-dec-2007',9883422,'INACTIVE');

 

alter table customer
modify email_id char(35);

SQL> select * from customer;

       CID          CNAME           EMAIL_ID          DOB             PHONE_NO       STATUS
      --------        ---------------     ---------------      ---------               ----------                    ----------
      1001           rakesh kumar    raj@gmail.com    10-JAN-99    313122                    ACTIVE
      1002          rajiv shyam        ra@gmail.com    17-JUN-87    2223453                  ACTIVE
      1003          sunil jha           sun@yahoo.com   12-FEB-55    1243565                 INACTIVE
      1111          raman shyam   ram@gmail.com   19-MAR-03   1113422                 ACTIVE
      1099           vijay goal            vi@rediff.com   17-DEC-07    9883422                INACTIVE

create table orders(orders_no number(5) primary key,order_date date,total_qty number(4,3),
total_price number(5,2),cid number(10) references customer,status char(20));

insert into orders values(11,'20-jun-2008',03,200,1001,'ACTIVE');
insert into orders values(21,'12-may-2004',02,350,1002,'ACTIVE');
insert into orders values(31,'28-dec-2006',07,150,1003,'INACTIVE');
insert into orders values(41,'17-jun-2002',02,180,1099,'ACTIVE');

SQL> select * from orders;

 ORDERS_NO ORDER_DAT    TOTAL_QTY TOTAL_PRICE     CID          STATUS
    ----------        ---------                   ----------             -----------                 ----------       ----------
        11            20-JUN-08                3                    200                          1001           ACTIVE
        21            12-MAY-04               2                    350                          1002           ACTIVE
        31            28-DEC-06                7                    150                          1003            INACTIVE
        41            17-JUN-02               2                     180                          1099            ACTIVE

SQL> insert into orders values(48,'17-jun-2002',02,180,1099,'ACTIVE');

1 row created.

SQL> insert into orders values(43,'17-jun-2002',02,180,1032,'ACTIVE');
insert into orders values(43,'17-jun-2002',02,180,1032,'ACTIVE')
*ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C002768) violated - parent key not
found

create table address(cid number(5) references customer,aid number(10) primary key,street char(10),city char(10),

state char(10),country char(10));

insert into address values(1001,100,'jaynagar','bangalore','karnataka','india');
insert into address values(1002,200,'sadashiv','hydrabad','andhra pd','india');
insert into address values(1003,300,'mandipet','tumkur','karnataka','india');
insert into address values(1002,400,'rajendra','patna','bihar','india');
insert into address values(1099,500,'mahatma','mumbai','maharastra','india');

SQL> select * from address;

 
       CID        AID        STREET     CITY       STATE      COUNTRY
     ---------- ----------    ----------      ----------     ----------          ----------
      1001        100        jaynagar      bangalore   karnataka         india
      1002        200         sadashiv     hydrabad    andhra pd         india
      1003        300        mandipet    tumkur       karnataka          india
      1002        400         rajendra      patna         bihar                  india
      1099        500        mahatma   mumbai      maharastra         india

create table item(item_id number(10)primary key,order_item char(20),

order_no number(10) references orders,qty number(5),price number(10));

 
insert into item values(66,'rice',11,10,1900);
insert into item values(77,'gram',31,15,1700);
insert into item values(88,'glass',21,25,1200);
insert into item values(99,'tea','41',17,100);
insert into item values(55,'spoon',21,19,300);

SQL> select * from item;

   ITEM_ID    ORDER_ITEM      ORDER_NO    QTY           PRICE
----------         --------------------    ----------              ----------   ----------
        66            rice                         11                         10          1900
        77            gram                       31                         15          1700
        88            glass                       21                         25          1200
        99            tea                          41                         17            100
        55            spoon                     21                          19            300

 

Query:

  SQL>select orders_no,cid,total_price from orders

  where total_price between 100 and 150;

SQL> select orders_no,cid,total_price from orders

2        where total_price>=100 and total_price<=250;

3         

ORDERS_NO   CID             TOTAL_PRICE
----------             ----------               -----------
        11              1001                        200
        31              1003                        150
        41              1099                        180
        48              1099                        180

select * from address where city='bangalore'or city='mumbai'; 
select * from address where city in('bangalore','mumbai','hydrabad');

 SQL> select * from address where city='bangalore'or city='mumbai';

        CID        AID      STREET       CITY           STATE      COUNTRY

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

      1001       100         jaynagar       Bangalore   karnataka       india
      1099       500         mahatma      mumbai      maharastra      india

SQL> select * from address where city in('bangalore','mumbai','hydrabad');

       CID        AID    STREET     CITY       STATE      COUNTRY
   ----------    --------   ----------     ----------    ----------          ----------
      1001        100     jaynagar      bangalore   karnataka        india
      1002        200     sadashiv     hydrabad    andhra pd         india
      1099        500     mahatma    mumbai     maharastra        india

select * from address where street like '%s%';
select * from address where street like '_a%';

SQL> select * from address where street like '%s%'; 

       CID      AID   STREET     CITY       STATE      COUNTRY

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

      1002     200      sadashiv   hydrabad    andhra pd       india

SQL> select * from address where street like '_a%';

       CID      AID        STREET          CITY       STATE      COUNTRY
---------- ----------          ----------            ----------       ----------      --------
      1001      100          jaynagar            bangalore   karnataka     india
      1002      200          sadashiv         hydrabad    andhra pd     india
      1003      300          mandipet         tumkur       karnataka      india
      1002      400          rajendra           patna          bihar             india
      1099      500          mahatma          mumbai     maharastra    india
 

SQL> select * from address where street like

  2  'ma%';

     CID        AID        STREET     CITY       STATE      COUNTRY

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

      1003        300        mandipet    tumkur       karnataka        india

      1099        500        mahatma    mumbai     maharastra        india

select * from customer where status='ACTIVE';

SQL> select * from customer where status='ACTIVE';

       CID          CNAME           EMAIL_ID            DOB           PHONE_NO      STATUS
     --------         ---------------     ---------------                 ---------         ----------               ----------
      1001          rakesh kumar    raj@gmail.com      10-JAN-99      313122                ACTIVE
      1002          rajiv shyam       ra@gmail.com       17-JUN-87      2223453 ACTIVE
      1111           raman shyam    ram@gmail.com    19-MAR-03    1113422 ACTIVE

select * from orders order by total_price;

SQL> select * from orders order by total_price;

 ORDERS_NO   ORDER_        TOTAL_   TOTAL_        CID               STATUS

                             DAT                   QTY         PRICE
----------            ---------                  ----------       ----------        ----------           --------------
        31            28-DEC-06             7              150                 1003                  INACTIVE
        41            17-JUN-02             2               180                 1099                  ACTIVE
        48            17-JUN-02             2               180                 1099                 ACTIVE
        11            20-JUN-08             3               200                 1001                  ACTIVE
        21            12-MAY-04           2               350                 1002                  ACTIVE

select * from orders order by total_price desc;

SQL> select * from orders order by total_price desc;

 ORDERS_NO        ORDER_DAT     TOTAL_QTY      TOTAL_PRICE        CID      STATUS
----------                        ---------            ----------                        -----------                  ---------- --------------
        21                        12-MAY-04           2                           350                              1002    ACTIVE
        11                        20-JUN-08             3                           200                              1001    ACTIVE
        41                        17-JUN-02             2                           180                               1099   ACTIVE
        48                        17-JUN-02             2                           180                              1099    ACTIVE
        31                        28-DEC-06            7                           150                              1003    INACTIVE

select * from student where feebal=111.77 or feebal=190.82;

SQL> select * from student where feebal=111.77 or feebal=190.82;

       SID          SNAME           DOB           FEEBAL      PHONE
----------        ---------------      ---------        ----------         ----------
       111           ganesh                                  111.77
                        gandhi          31-MAR-08     111.77
       102           suman          10-JAN-87      190.82        198

select * from student where feebal in(111.77,190.82);

SQL> select * from student where feebal in(111.77,190.82); 

       SID          SNAME           DOB           FEEBAL      PHONE
----------         ---------------      ---------         ----------         ----------
       111            ganesh                                     111.77
                         gandhi          31-MAR-08       111.77
       102           suman           10-JAN-87         190.82        198