Working with Constraints in SQL

-- Data Integrity or Constraints
-- Ensuring data in the tables is unique, valid and consistent


-- 1) Not null Constraint
create table emp1(empid char(4) not null,emoname varchar(14) not null,empmobno varchar(14))
-- valid insert
insert into emp1 values('e1','aaa','1111')
insert into emp1 values('e2','bbb',null)
-- invalid insert
insert into emp1 values(null,'ccc','2222')
insert into emp1 values('e3',null,'3333')
sp_help emp1

-- 2) Default Constraint
create table emp2(empid char(4) not null,empname varchar(14) not null,empcity varchar(14) default 'Hyderabad')
-- Valid insert
insert into emp2 values('e1','aaa',default)
insert into emp2 values('e2','bbb','Delhi')
insert into emp2 values('e3','ccc',null)
insert into emp2 values('e4','ddd','Pune')
-- InValid insert
insert into emp2 values('e5','eee','Default')
select * from emp2
sp_help emp2

-- 3) Check Constraint
create table emp3(empid char(4),empname varchar(14),empcity varchar(14) check (empcity in ('Hyderabad', 'Pune','Chennai')),empsalary int)
-- Valid Insert
insert into emp3 values('e1','aaa','Pune', 25000)
-- Invalid Insert
insert into emp3 values('e2','bbb','Mysore', 25000)
select * from emp3
sp_help emp3

alter table emp3
drop constraint CK__emp3__empcity__0AD2A005

alter table emp3
drop constraint ckemp3salary

alter table emp3
add constraint ckemp3salary check (empsalary between 20000 and 30000)
-- Valid Insert
insert into emp3 values('e3','ccc','Mysore', 27000)
--Invalid Insert
insert into emp3 values('e4','ddd','Mysore', 37000)

create table emp4(empid char(4),empname varchar(14),empcity varchar(14) constraint ck_emp3city check (empcity in ('Hyderabad', 'Pune','Chennai')),empsalary int)
sp_help emp3

-- Table level constraint
create table emp5(empid char(4),empname varchar(14),empcity varchar(14),empsalary int, constraint ckemp5city check (empcity in ('Hyderabad', 'Pune','Chennai')), constraint ckemp5sal check (empsalary between 20000 and 30000))

-- 4) Primary key Constraint
create table emp7(eid char(4) primary key,ename varchar(14),ecity varchar(14))
-- Valid Insert
insert into emp7 values('e1','a','aa')
insert into emp7 values('e2','b','bb')
insert into emp7 values('e3','c','cc')
--Invalid Insert
insert into emp7 values('e1','d','dd') //Because eid e1 already exist.
insert into emp7 values(null,'e','ee')
select * from emp7

create table emp8(eid char(4),ename varchar(14),ecity varchar(14))
alter table emp8
alter column eid char(4) not null

alter table emp8
add constraint pkeidemp8 primary key (eid)
--Invalid Insert
insert into emp8 values(null,'e','ee')

--5) Unique constraint (Similar as Primary Key Constraint except the unique column can also have atmost 1 null entry)
create table emp9(eid char(4) primary key,panid varchar(10) unique,mobileno varchar(10) unique)
--Valid Insert
insert into emp9 values ('e1','P1','M1')
insert into emp9 values ('e2','P2',null)
insert into emp9 values ('e3',null,'M2')
--InValid Insert
insert into emp9 values ('e4','P3','M1')
insert into emp9 values ('e5','P4',null)
insert into emp9 values ('e6',null,'M3')
insert into emp9 values ('e6','P2','M4')

--6) Foreign key Constraint
select * from employ
select * from department
alter table employ
alter column empid char(4) not null

alter table employ
add constraint pkempid primary key(empid)

alter table department
alter column deptid char(4) not null

alter table department
add constraint pkdeptid primary key(deptid)

alter table employ
add constraint fkempdeptid foreign key(deptid) referencesdepartment (deptid)

create table emp11(eid char(4) primary key,panid varchar(10) unique,mobileno varchar(10) unique,mid
char(4) foreign key references mgr(mid))

sp_help emp11

create table mgr(mid char(4) primary key,mname varchar(10))
insert into mgr values('M1','abc')
select * from mgr
insert into emp11 values('E12','P7','M7','M2')
select * from emp11

Cheers..!!
https://techiesss.blogspot.in/