Constraints

The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.

Integrity constraints are used to ensure accuracy and consistency of data in relational data base 

Two types of constraints exists

1.Column level constraints 

2.Table level constraints

1.COLUMN LEVEL CONSTRAINTS :

They are imposed on single column ,the constraints are applied while table creation and they can not be applied if a constraint spans more than one column in a table

Syntax :

Column name datatype <constraint_name>   

Example:

SQL> create table std(sno number(2) primary key,sname varchar(11));

2.TABLE LEVEL CONSTRAINTS :

These are applied on more than one column in a table.

Syntax :

Constraint  [<constraint_name>] <constraint_types>

Example on single column:

SQL> create table std(sno number(2),sname varchar(11),primary key(sno));

OR

SQL> create table std(sno number(2),sname varchar(11),constraint pk_std primary key(sno));

Example on more than one column:

SQL> create table std(sno number(2),sname varchar(11),primary key(sno,sname));

                                                    OR

SQL> create table std(sno number(2),sname varchar(11),constraint pk_std primary key(sno,sname));

TYPES OF CONSTRAINTS :

• NOT NULL
• UNIQUE
• DEFAULT
• CHECK
• Key Constraints – PRIMARY KEY, FOREIGN KEY  

NOT NULL:

NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t provide value for a particular column while inserting a record into a table, it takes NULL value by default. By specifying NULL constraint, we can be sure that a particular column(s) cannot have NULL values.

Example:

SQL> create table abc(sno number(2) not null,sname varchar(11));
Table created.
SQL> insert into abc values(11,'raj');
1 row created.
SQL> insert into abc(sname) values('raj');
insert into abc(sname) values('raj')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."ABC"."SNO")

UNIQUE:

UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has a unique constraint, it means that particular column cannot have duplicate values in a table.

Example:

SQL> create table abc(sno number(2) unique,sname varchar(11));
Table created.
SQL> insert into abc values(11,'raj');
1 row created.
SQL> insert into abc values(11,'raj');
insert into abc values(11,'raj')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004058) violated

DEFAULT:

The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table.

Example :

SQL> create table abc(sno number(2) unique,sname varchar(22),city varchar(11) default('vsp'));
Table created.
SQL> insert into abc values(11,'raj','hyd');
1 row created.
SQL> insert into abc values(11,'raj',default);
insert into abc values(11,'raj',default)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004060) violated

SQL> insert into abc values(12,'raj',default);
1 row created.
SQL> insert into abc(sno,sname) values(13,'rasdf');
1 row created.
SQL> select * from abc;
       SNO SNAME                  CITY
---------- ---------------------- -----------
        11 raj                    hyd
        12 raj                    vsp
        13 rasdf                 vsp

CHECK:

This constraint is used for specifying range of values for a particular column of a table. When this constraint is being set on a column, it ensures that the specified column must have the value falling in the specified range.

Example :

SQL> create table abc(sno number(2) unique,sname varchar(22),city varchar(11) check(city in('vsp','hyd')));
Table created.
SQL> insert into abc values(11,'raj','vsp');
1 row created.
SQL> insert into abc values(22,'raj','vizag');
insert into abc values(22,'raj','vizag')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C004061) violated

Key Constraints – PRIMARY KEY, FOREIGN KEY

1.Primary key :A key that is used for unique identification of each row in a table is known as primary key of the database.

Example :

SQL> create table std(sno number(2),sname varchar(11),constraint pk_std primary key(sno));
Table created.
SQL> insert into std values(11,'raju');
1 row created.
SQL> insert into std values(22,'rani');
1 row created.
SQL> select * from std;
       SNO SNAME
---------- -----------
        11 raju
        22 rani
SQL> insert into std values(11,'ramesh');
insert into std values(11,'ramesh')
*
ERROR at line 1:
ORA-00001: unique constraint (RK.PK_STD) violated

2. Foreign  Key :Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

Example :

SQL> create table std(sno number(2),sname varchar(11),course varchar(7),cid number(3),primary key(sno));

SQL> select * from std;

       SNO SNAME       COURSE         CID
---------- ----------- ------- ----------
        11 raju           mpc           520
        22 rani           bipc           420
        33 suresh       mpc           520
        44 ramesh      bipc           420
        55 sita            mpc          520

SQL> Create table faculty (fid number(3)  primary key, fname varchar2(15),  sno number(2), foreign key (sno) references  std(sno));

SQL> select * from faculty;
       FID FNAME                  SNO
---------- --------------- ----------
       111 rk                      11
       222 pk                      44

Relational set operators based on the above 2 tables ( std and faculty):

SQL> select sno from std union select sno from faculty ;

       SNO
----------
        11
        22
        33    
        44
        55

SQL> select sno from std union all select sno from faculty ;

       SNO

----------

        11

        22

        33

        44

        55

        11

        44

7 rows selected.

SQL> select sno from std intersect select sno from faculty ;

       SNO

----------

        11

        44

SQL> select sno from std minus select sno from faculty ;

       SNO

----------

        22

        33

        55

SQL> select sno from faculty minus select sno from std ;

no rows selected

keys

Note:

1.Primary key is a subset of Candidate key 

2.Candidate key is a subset of Super key 

3.Every Candidate key is a super key but every Super key is not a candidate key 

1. SUPER  KEY:
A Super key is a combination of all possible attributes that can uniquely identify the rows or tuples in the given relation 

• Super key is a superset of a candidate key.
• A table can have as many as super keys.

Note: 

If the given table is with n attributes (eg: for 5 attributes ) then the maximum super key present in the given table is

2^n - 1

2^5 - 1= 31 

Example:

2.CANDIDATE KEY:

It is defined as a minimal super key (or)  irreducible super key.(or) An attribute (or) A combination  of attribute that identifies the record  uniquely  but none of its proper subset can identify the record uniquely.

Example: sid

sname, saddress

Here sid key can identified the record uniquely.

Similarly combination of sname and saddress  can identified the record uniquely  but neither sname (nor) saddress used to identify the record uniquely.

Note: 

1.Super key whose proper subset is not a super key

2.Minimal super key 

Example:

s1={1,2,3}  and s2 = {1,2}

subset (⊆) : All the members of s2 must be in s1

s2 ⊆ s1

Proper subset (⊂ ) :All the members of s2 must be in s1 and All the members of s1 must not be in s2

s2 ⊆ s1 and s1 ⊈ s2

Example:

s1={1,2,3}  and s2 = {1,2,3}

subset (⊆) : All the members of s2 must be in s1

s2 ⊆ s1

Proper subset (⊂ ) :All the members of s2 must be in s1 and All the members of s1 must not be in s2

s2 ⊆ s1 (T) and s1 ⊈ s2 (F) so this is not a proper subset

3.ALTERNATE KEY:

Out of all candidate keys, only one gets selected as primary key, remaining keys are known as Alternate keys 

Example:

In the Employee table.

Emp_id is best suited for the primary key

Rest of the attributes like Aadhar_No, Email_id are considered as a Alternate keys.

4.PRIMARY KEY :

A Primary key is one of the candidate key chosen by the database designer to uniquely identify the tuples in the relation 

• The value of Primary key can never be NULL.
• The value of Primary key must always be unique(not duplicated)
• The value of Primary key can never be changed i.e no updation is possible.
• A relation is allowed to have only one Primary key.

Example:

5.Foreign  (or)  referential key (or) integrity  :

A foreign key is an attribute  (or)  combination of attributes in one base table that refers to others key of another table. The purpose of the foreign keys is to ensure the referential integrity of the data  that which appear in the database are permitted.

Note: Let R1(a,b,c) and R2(x,y,z) be two relations in which 'a' is foreign key in R1 that refers to primary key of R2.consider four options

a) Insert into R1

b) Insert into R2

c) Delete from R1

d) Delete from R2

which is correct regarding referential integrity?

1) option a and b will cause violation

2) option b and c will cause violation

3) option c and d will cause violation

4) option d and a will cause violation

Ans: R1(a,b,c) [Referencing Table] with 'a' --> "FK" and R2(x,y,z) [Base Referenced Table] with 'x' --> PK

6. Composite key:

If we use multiple attributes to create a primary key then such type of keys are called composite key.

Eg : std  (sid,  sname ,sadd)

Sub (subid,subname,credit,sid)

Marks(sid sname subid  subname  marks results)

 

Relational Set Operators

Set Operators:

The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation

Union

Union All

Intersect

Minus

consider table1 (std1):

SQL> select * from std11;

       SNO SNAME

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

        11 raju

        22 rani

        33 vani

consider table2 (std2):

SQL> select * from std22;

       SNO SNAME

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

        33 vani

        44 suresh

        55 mahesh

Union:

SQL> select * from std11 union select * from std22;

       SNO SNAME

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

        11 raju

        22 rani

        33 vani

        44 suresh

        55 mahesh

Union all:

SQL> select * from std11 union all select * from std22;

       SNO SNAME

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

        11 raju

        22 rani

        33 vani

        33 vani

        44 suresh

        55 mahesh

Intersect:

SQL> select * from std11 intersect select * from std22;

       SNO SNAME

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

        33 vani

Minus:

SQL> select * from std11 minus select * from std22;

       SNO SNAME

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

        11 raju

        22 rani

SQL> select * from std22 minus select * from std11;

       SNO SNAME

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

        44 suresh

        55 mahesh

Relational Set Operators:

A query language is a language in which user request information from the  database  that  can  be  categorized  as  either  procedural  or  non-procedural .

Procedural Language:(Pl/sql)

      The user instructs the system to do a sequence of operations on a  database  to  compute  the  desired  result .

Non-Procedural Language:(sql)

       The  user  describes  the  desire  information  without  giving  any  specific  procedure  for  obtaining  the  information  in  a  database .

Relational algebra: 

     Relational  algebra  is   a  procedural  query  language  which  consist  of  set  of  operations  that  take  one  or  two  relations  as  input  and  produces  a  new  relation  as  an  output . 

Table 1:

SQL>create table course(cid number(2) primary key,cname varchar(5));

SQL> select * from course ;
       CID CNAME
---------- -----
        11 mca
        22 bca
        33 mpcs
        44 mecs
        55 mscs

Table 2:

SQL>create table std(sno number(3),sname varchar(11),city varchar(10),cid number(2),foreign key(cid) references course(cid));

SQL> select * from std ;
       SNO SNAME       CITY              CID
---------- ----------- ---------- ----------
       111 raju        vsp                   11
       222 rani        hyd                   11
       333 vani        vzm                  22
       444 kani        rjm                   33
       555 poni        anp                   33

1.Union :This  operator  combines  all rows  of  one  table  with  all  the rows  of  another  table  except  for   duplicate  tuples .

Syntax : Select<statement1> Union Select <statement2>;

Example :

SQL> select cid from course  union  select cid from std;
       CID
----------
        11
        22
        33
        44
        55

2.Union all :  It  accepts  all  the  duplicate  values

Syntax : Select<statement1> Union all Select <statement2>;

Example :

SQL> select cid from course  union all select cid from std;
       CID
----------
        11
        22
        33
        44
        55
        11
        11
        22
        33
        33

3.Intersect :  This  operator  takes  two  tables  and  combines  only  the  tuples  that  appear  in  both  tables .

Syntax :  select <statement 1> intersect select <statement 2>;

Example :

SQL> select cid from course  intersect select cid from std;
       CID
----------
        11
        22
        33

4.Difference :  This  operator  gets  all  rows  in  one  table  that  are  not  found  in  another  table 

Syntax :  select <statement 1> minus select <statement 2>;

Example :
SQL> select cid from course  minus select cid from std;
       CID
----------
        44
        55

Wild card characters

Wild card characters                 Description

%                                A substitute for zero (or) more Characters 

_(underscore)             A substitute for single character

A wild card character can substitute any other character in a string in SQL.

These are used with ‘like’ operator these are used to search for specific data within a table

Ex 1. Select all customers in a city starting with l followed by any character, followed by n and followed by any character (fixed length).

Query:

select * from customer where city like 'l-n-----‘;

Ex 2. List the names of customers whose name start with letter n followed by a character and followed by variable length.

Query :

Select  *  from  customer  where  name  like  ‘ n_% ‘ ;

Ex 3 . List  all  customers  whose  name  starts  with  a 

Query :

Select  *  from  customer  where  name  like  'a%' ;

Ex 4 . List  all  customers  whose  name  ends  with  a

Query :

Select  *  from  customer  where  name  like  '%a';

Special Operator:

Operator               Description

like            Pattern  matching  from  a column

in               To  check  the  value  within  the  set

between    To  check  the  value  within  a range

Ex : 1 . List  the  employees  whose  name  starts  with  letter  K

Query :

select  *  from  employee whose  name  like  'K%';

2 . List  the  name  of  employees who  belong  to  the  departments  of  10  and    20 .

Query :

select  ename  from  employee where  deptno  in(10,20);

3 . List  the  employees  who  does  not  belong  to  the  departments  of  sales  and  marketing .

Query :

select  *  from  employee  where  deptname  not in  (‘sales’,’marketing’);

4 . List  the  employee number  , employee name  and  salary  of  the employee whose  salary  ranges  from  10,000  to  50,000

Query :

select  empno, empname , sal  from  employee where sal  between (10,000  to  50,000);

Example:

like , not like:

SQL> select * from product;
       PID PNAME                       QTY      PRICE     EMAIL
---------- -------------------- ---------- ---------- --------------------
       111 raj                                 2        123     sa@gm
       222 kumar                            3        234     ds@y
       333 rani                                7        542     ff@y
       444 joy                                 5        243     hg#df

SQL> select * from product where pname like 'r%';
       PID PNAME                       QTY    PRICE         EMAIL
---------- -------------------- ---------- ---------- --------------------
       111 raj                             2        123             sa@gm
       333 rani                           7        542             ff@y

SQL> select * from product where pname like 'r__';
       PID PNAME                       QTY      PRICE         EMAIL
---------- -------------------- ---------- ---------- --------------------
       111 raj                           2        123             sa@gm

SQL> select * from product where pname like '__r';
no rows selected

SQL> select * from product where pname like '__j';
       PID PNAME                       QTY      PRICE         EMAIL
---------- -------------------- ---------- ---------- --------------------
       111 raj                           2        123             sa@gm

SQL> select * from product where pname like '%%a';
no rows selected

SQL> select * from product where pname like '%a%';
       PID PNAME                       QTY      PRICE         EMAIL
---------- -------------------- ---------- ---------- --------------------
       111 raj                           2        123             sa@gm
       222 kumar                      3        234             ds@y
       333 rani                          7        542             ff@y

SQL> select * from product where pname not like '%a%';
       PID PNAME                       QTY      PRICE         EMAIL
---------- -------------------- ---------- ---------- --------------------
       444 joy                           5        243             hg#df

SQL> select * from product ;
       PID PNAME                       QTY      PRICE         EMAIL
---------- -------------------- ---------- ---------- --------------------
       111 raj                           2        123             sa@gm
       222 kumar                      3        234 ds@y
       333 rani                          7        542 ff@y
       444 joy                           5        243 hg#df

in , not in :

SQL> select * from product where pid in (111,444) ;
       PID PNAME                       QTY      PRICE EMAIL
---------- -------------------- ---------- ---------- --------------------
       111 raj                           2        123 sa@gm
       444 joy                           5        243 hg#df

SQL> select * from product where pid not in (111,444) ;
       PID PNAME                       QTY      PRICE EMAIL
---------- -------------------- ---------- ---------- --------------------
       222 kumar                      3        234 ds@y
       333 rani                          7        542 ff@y

between , not between :

SQL> select * from product where pid between 222 and 333 ;
       PID PNAME                       QTY      PRICE EMAIL
---------- -------------------- ---------- ---------- --------------------
       222 kumar                      3        234 ds@y
       333 rani                          7        542 ff@y

SQL> select * from product where pid not between 222 and 333 ;
       PID PNAME                       QTY      PRICE EMAIL
---------- -------------------- ---------- ---------- --------------------
       111 raj                           2        123 sa@gm
       444 joy                           5        243 hg#df