Pages

Showing posts with label Constraints. Show all posts
Showing posts with label Constraints. Show all posts

Tuesday, 28 September 2021

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

Conflict Serializability

Find out conflict serializability for the given transactions T1 T2 T3 R(X)     ...