220 likes | 511 Views
INTEGRITY CONSTRAINTS. create table sale(sorderno number(20) primary key, sdate date,clientno number(30),smanno number(20));. Column level. create table sale1(sorderno number(20), sdate date,clientno number(30),smanno number(20),primary key(sorderno, clientno));. Table level.
E N D
create table sale(sorderno number(20) primary key, sdate date,clientno number(30),smanno number(20)); Column level
create table sale1(sorderno number(20), sdate date,clientno number(30),smanno number(20),primary key(sorderno, clientno)); Table level
Create table mathu(regno number(6) primary key, name varchar2(8)); desc mathu; Foreign key
create table mitha (regno number(6)references mathu, name varchar2(8));
insert into mathu values(101,’jey’); insert into mathu values(102,'jeni'); insert into mathu values(103,’magesh’); select * from mathu;
insert into mitha values('101',priya’); Insert into mitha values(1008,’sudha’); Output: Insert into mitha values(1008,'sudha') * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.SYS_C005320) violated - parent key not found
Constraints are policies used to maintain accuracy and integrity of data in the database. Types of constraints Domain integrity constraints Entity integrity constraints Refferential integrity constraints operational constraints. constraints
Domain integrity constraints are used to verity whether the data entered is in proper form and range. NOT NULL CONSTRAINTS CHECK CONSTRAINTS Domain integrity constraints
It is used to make the users to enter values to the fields compulsory. NOT NULL CONSTRAINTS
Create table emp ( a number not null, b number null, c number ); insert into emp values ( 1, null, null); insert into emp values ( 2, 3, 4); insert into emp values (null, 5, 6); Example(column level)
Create table hostel(sno number(3) constraints rno notnull,name varchar2(8) ,address varchar2(10)); Using constraints
Create table hostel(sno number(8) ,name varchar2(8),address varchar2(8),notnull(sno); Example(Table level constraints)
Create table hostel(sno number(8) ,name varchar2(8),address varchar2(8),constraints cno notnull(sno); Using constraints
Check constraints is used to enforce an integrity rule based on a logical or boolean expression. • The following are some operators used in check constraints IN NOTIN BETWEEN LIKE Check constraints
IN: Check(sex in(‘male’,’female’)) NOTIN: Check(name notin(‘rame’,’babu’)); BETWEEN: Check(description like(‘r%’) EXAMPLE(IN,NOTIN,BETWEEN,LIKE)
Create table student(rollno number(4),constraints crol check(rollno between 100 and 1000),name varchar2(30),sex varchar2(7),check in(‘male’,’female’)); Column level constraints
Create table student(rollno number(4),name varchar2(10),age number(2),dob date,doad date,constrints date check(dob>doad)); Table level
MSPVL POLYTECHNIC COLLEGE PAVOORCHATRAM Thank you!