About Lesson
SELECT TOP (1000) [ID]
,[Gender]
FROM [sircldb].[dbo].[tblGender]
— syntax: delete from tableName where condition;
delete from tblGender where id=3;
/*
Cascading referential integrity constraint
1.No Action
2. Cascade
3. Set NULL
4. Set Default
The DELETE statement conflicted with the REFERENCE constraint “FK_tblPersons_tblGender”.
The conflict occurred in database “sircldb”, table “dbo.tblPersons”, column ‘GenderId’.
The statement has been terminated.
Check Constraint – Limit Value in a column according to expression
Syntax:
ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME } CHECK ( BOOLEAN_EXPRESSION )
The following check constraint, limits the age between ZERO and 150.
ALTER TABLE tblPerson
ADD CONSTRAINT CK_tblPerson_Age CHECK (Age > 0 AND Age < 150)
*/
–after set cascade effect – Set Null
delete from tblGender where id=3;
select * from tblPersons;
update tblPersons set Age=180 where id=4;
/*
The UPDATE statement conflicted with the CHECK constraint “CK_tblPersons_Age”.
The conflict occurred in database “sircldb”, table “dbo.tblPersons”, column ‘Age’.
The statement has been terminated.
Age > 0 AND Age < 150
*/
update tblPersons set Age=85 where id=4;
select * from tblPersons;
/*
To drop the CHECK constraint:
ALTER TABLE tableName
DROP CONSTRAINT Constraint_Name
*/
sp_help tblPersons;
alter table tblPersons drop constraint CK_tblPersons_Age;
sp_help tblPersons;
update tblPersons set Age=180 where id=4;
select * from tblPersons;
insert into tblPersons values(‘Hardeep’,’har@deep.com’,20,1);
select * from tblPersons;
insert into tblPersons values(6,’Sita’,’sita@abc.com’,20,2);
/*
An explicit value for the identity column in table ‘tblPersons’
can only be specified when a column list is used and IDENTITY_INSERT is ON.
*/
insert into tblPersons values(‘Sita’,’sita@abc.com’,20,2);
select * from tblPersons;
delete from tblPersons where id=7;
SET Identity_Insert tblPersons ON;
insert into tblPersons(ID,Name,email,age,GenderId) values(6,’Sita’,’sita@abc.com’,20,2);
SET Identity_Insert tblPersons OFF;
select * from tblPersons;
insert into tblPersons values(‘Radha’,’radha@abc.com’,20,2);
delete from tblPersons;
select * from tblPersons;
/*
f you have deleted all the rows in a table, and you want to reset the identity column value,
use DBCC CHECKIDENT command. This command will reset PersonId identity colum
*/
DBCC CHECKIDENT(tblPersons, RESEED, 0)
insert into tblPersons values(‘Radha’,’radha@abc.com’,20,2);