Scribd
Upload
15 views17 pages

Baby Dbms

Chalo padho ishe

Uploaded by

sanayasumpir
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views17 pages

Baby Dbms

Chalo padho ishe

Uploaded by

sanayasumpir
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

UNIT 2

Lecture 13
Integrity Constraints on SQL

Dinesh Kumar Bhawnani


Bhilai Institute of Technology, DURG
Integrity Constraints on SQL
•UNIQUE
•NOT NULL
•PRIMARY KEY
•CHECK
•DEFAULT
•FOREIGN KEY
Dinesh Kumar Bhawnani, BIT DURG
DDL, CREATE STATEMENT
create table student STUDENT
(rollno number(3),
Rollno Sname Sem Branch Marks Pno
1 RAM 3 CSE 40 121

sname varchar2(20), 2
1
SHYAM
MOHAN
5
7
CSE
CSE
50
55
122
123
sem number(1), 2 GOPAL 5 IT 65 121
1 RAM 3 CSE 40 121
branch varchar2(20), 100 PINKI 9 ETC 90 123

marks number(2), 1000


NULL
DINESH
RAM
5
3
CSE
CSE
55
40
121
121
pno number(3) NULL NULL NULL NULL NULL NULL

);
Dinesh Kumar Bhawnani, BIT DURG
UNIQUE CONSTRAINT (Column level)
create table student STUDENT
Rollno Sname Sem Branch Marks Pno
(rollno number(3) UNIQUE, 1 RAM 3 CSE 40 121

sname varchar2(20), 2 SHYAM 5 CSE 50 122


1 MOHAN 7 CSE 55 123
sem number(1), 2 GOPAL 5 IT 65 121

branch varchar2(20), 1
100
RAM
PINKI
3
9
CSE
ETC
40
90
121
123
marks number(2), 1000 DINESH 5 CSE 55 121

pno number(3) NULL


NULL
RAM
NULL
3 CSE
NULL NULL
40
NULL
121
NULL
);
Dinesh Kumar Bhawnani, BIT DURG
UNIQUE CONSTRAINT (Table level)
create table student STUDENT
Rollno Sname Sem Branch Marks Pno
(rollno number(3), 1 RAM 3 CSE 40 121

sname varchar2(20), 2 SHYAM 5 CSE 50 122


1 MOHAN 7 CSE 55 123
sem number(1), 2 GOPAL 5 IT 65 121

branch varchar2(20), 1
100
RAM
PINKI
3
9
CSE
ETC
40
90
121
123
marks number(2), 1000 DINESH 5 CSE 55 121

pno number(3), NULL


NULL
RAM
NULL
3 CSE
NULL NULL
40
NULL
121
NULL
Unique (rollno)
);
Dinesh Kumar Bhawnani, BIT DURG
UNIQUE CONSTRAINT (Table level)
create table student STUDENT
Rollno Sname Sem Branch Marks Pno
(rollno number(3), 1 RAM 3 CSE 40 121

sname varchar2(20), 2 SHYAM 5 CSE 50 122


1 MOHAN 7 CSE 55 123
sem number(1), 2 GOPAL 5 IT 65 121

branch varchar2(20), 1 RAM


100 PINKI
3
9
CSE
ETC
40
90
121
123
marks number(2), 1000 DINESH 5 CSE 55 121

pno number(3), NULL RAM


NULL NULL
3 CSE
NULL NULL
40
NULL
121
NULL
Constraint uni_con Unique (rollno)
);
Dinesh Kumar Bhawnani, BIT DURG
NOT NULL CONSTRAINT
create table student STUDENT
Rollno Sname Sem Branch Marks Pno
(rollno number(3) NOT NULL, 1 RAM 3 CSE 40 121
2 SHYAM 5 CSE 50 122
sname varchar2(20), 1 MOHAN 7 CSE 55 123

sem number(1), 2
1
GOPAL
RAM
5
3
IT
CSE
65
40
121
121
branch varchar2(20), 100 PINKI 9 ETC 90 123
1000 DINESH 5 CSE 55 121
marks number(2), NULL RAM 3 CSE 40 121

pno number(3) NULL NULL NULL NULL NULL NULL

);
Dinesh Kumar Bhawnani, BIT DURG
NOT NULL & UNIQUE CONSTRAINT
create table student
(rollno number(3) NOT NULL UNIQUE, STUDENT
sname varchar2(20), Rollno Sname Sem Branch Marks Pno
1 RAM 3 CSE 40 121
sem number(1), 2 SHYAM 5 CSE 50 122
branch varchar2(20), 1 MOHAN 7 CSE 55 123
2 GOPAL 5 IT 65 121
marks number(2),
1 RAM 3 CSE 40 121
pno number(3) 100 PINKI 9 ETC 90 123

); 1000 DINESH 5 CSE 55 121


NULL RAM 3 CSE 40 121
NULL NULL NULL NULL NULL NULL

Dinesh Kumar Bhawnani, BIT DURG


PRIMARY KEY CONSTRAINT
create table student
(rollno number(3) primary key, STUDENT
Rollno Sname Sem Branch Marks Pno
sname varchar2(20), 1 RAM 3 CSE 40 121
sem number(1), 2 SHYAM 5 CSE 50 122

branch varchar2(20), 1 MOHAN 7 CSE 55 123


2 GOPAL 5 IT 65 121
marks number(2), 1 RAM 3 CSE 40 121

pno number(3) 100 PINKI 9 ETC 90 123


1000 DINESH 5 CSE 55 121
); NULL RAM 3 CSE 40 121
NULL NULL NULL NULL NULL NULL

Dinesh Kumar Bhawnani, BIT DURG


PRIMARY KEY CONSTRAINT
create table student
(rollno number(3), STUDENT
Rollno Sname Sem Branch Marks Pno
sname varchar2(20), 1 RAM 3 CSE 40 121
sem number(1), 2 SHYAM 5 CSE 50 122

branch varchar2(20), 1 MOHAN 7 CSE 55 123


2 GOPAL 5 IT 65 121
marks number(2), 1 RAM 3 CSE 40 121

pno number(3), 100 PINKI 9 ETC 90 123


1000 DINESH 5 CSE 55 121
Constraint pkey Primary key (rollno) NULL RAM 3 CSE 40 121
); NULL NULL NULL NULL NULL NULL

Dinesh Kumar Bhawnani, BIT DURG


COMPOSITE PRIMARY KEY CONSTRAINT
create table student STUDENT
(rollno number(3) PRIMARY KEY, Rollno Sname Sem Branch Marks Pno
1 RAM 3 CSE 40 121
sname varchar2(20) PRIMARY KEY, 2 SHYAM 5 CSE 50 122
sem number(1), 1 MOHAN 7 CSE 55 123

branch varchar2(20), 2 GOPAL 5 IT 65 121


1 RAM 3 CSE 40 121
marks number(2), 100 PINKI 9 ETC 90 123
pno number(3) 1000 DINESH 5 CSE 55 121

);

Dinesh Kumar Bhawnani, BIT DURG


COMPOSITE PRIMARY KEY CONSTRAINT
create table student STUDENT
(rollno number(3), Rollno Sname Sem Branch Marks Pno
1 RAM 3 CSE 40 121
sname varchar2(20), 2 SHYAM 5 CSE 50 122

sem number(1), 1 SHYAM 7 CSE 55 123


2 RAM 5 IT 65 121
branch varchar2(20), 1 RAM 3 CSE 40 121
marks number(2), 2 SHYAM 5 CSE 50 122

pno number(3), NULL NULL NULL NULL NULL NULL


NULL PINKI 9 ETC 90 123
PRIMARY KEY (rollno, sname) 100 NULL 5 CSE 55 121

); 3 GOPAL 7 CSE 55 123

Dinesh Kumar Bhawnani, BIT DURG


CHECK CONSTRAINT
create table student STUDENT
(rollno number(3), Rollno Sname Sem Branch Marks Pno
1 RAM 3 CSE 40 121
sname varchar2(20),
2 SHYAM 5 CSE 50 122
sem number(1) CHECK (SEM >= 1 AND SEM <=8),
1 MOHAN 7 CSE 55 123
branch varchar2(20),
2 GOPAL 5 IT 65 121
marks number(2), 1 RAM 3 CSE 40 121
pno number(3) 100 PINKI 9 ETC 90 123
); 1000 DINESH 5 CSE 55 121
NULL RAM 3 CSE 40 121
NULL NULL NULL NULL NULL NULL

Dinesh Kumar Bhawnani, BIT DURG


CHECK CONSTRAINT
create table student STUDENT
(rollno number(3), Rollno Sname Sem Branch Marks Pno
1 RAM 3 CSE 40 121
sname varchar2(20),
2 SHYAM 5 CSE 50 122
sem number(1),
1 MOHAN 7 CSE 55 123
branch varchar2(20),
2 GOPAL 5 IT 65 121
marks number(2), 1 RAM 3 CSE 40 121
pno number(3), 100 PINKI 9 ETC 90 123
CHECK (SEM >= 1 AND SEM <=8) 1000 DINESH 5 CSE 55 121
); NULL RAM 3 CSE 40 121
NULL NULL NULL NULL NULL NULL

Dinesh Kumar Bhawnani, BIT DURG


CHECK CONSTRAINT
create table student STUDENT
(rollno number(3), Rollno Sname Sem Branch Marks Pno
1 RAM 3 CSE 40 121
sname varchar2(20),
2 SHYAM 5 CSE 50 122
sem number(1) CHECK SEM between 1 AND 8,
1 MOHAN 7 CSE 55 123
branch varchar2(20),
2 GOPAL 5 IT 65 121
marks number(2), 1 RAM 3 CSE 40 121
pno number(3) 100 PINKI 9 ETC 90 123
); 1000 DINESH 5 CSE 55 121
NULL RAM 3 CSE 40 121
NULL NULL NULL NULL NULL NULL

Dinesh Kumar Bhawnani, BIT DURG


DEFAULT CONSTRAINT
create table student STUDENT
Rollno Sname Sem Branch Marks Pno
(rollno number(3),
1 RAM 3 CSE 40 121
sname varchar2(20),
2 SHYAM 5 CSE 50 122
sem number(1), 1 MOHAN 7 CSE 55 123
branch varchar2(20) DEFAULT ‘CSE’, 2 GOPAL 5 IT 65 121
marks number(2), 1 RAM 3 CSE 40 121
pno number(3) 100 PINKI 9 ETC 90 123

); 1000 DINESH 5 CSE 55 121


NULL RAM 3 CSE 40 121
NULL NULL NULL NULL NULL NULL

insert into student (rollno, sname, sem, marks, pno) values (1, ’RAM’, 3, 40, 121);

Dinesh Kumar Bhawnani, BIT DURG


For Video lecture on this topic please subscribe to my youtube channel.

The link for my youtube channel is

https://www.youtube.com/channel/UCRWGtE76JlTp1iim6aOTRuw?sub
_confirmation=1

Dinesh Kumar Bhawnani, BIT DURG

You might also like