Foreign key constraint with nocheck

Sample query to validate how foreign key with nocheck and on delete cascade works.

-- create test tables
create table students (
	student_id char(4) primary key,
	student_name varchar(100) not null,
	dept_code char(3)
)

create table dept (
	dept_code char(3) primary key,
	dept_name varchar(100)
)

-- insert test data
insert into dept values ('EEE', 'Electrical and Electronics Engineering')
insert into dept values ('CSE', 'Computer Science Engineering')
insert into dept values ('FIN', 'Finance')

insert into students values ('1001','Aditya',NULL)
insert into students values ('1002', 'Bala','CHE')
insert into students values ('1003', 'Ram','FIN')

select * from dept
select * from students

-- existing foreign key references are not checked when the below query exeucted.
ALTER TABLE [dbo].[students] WITH NOCHECK 
ADD CONSTRAINT [fk_students_student_id] 
	FOREIGN KEY ([dept_code]) REFERENCES [dbo].[dept] ([dept_code]) ON DELETE CASCADE;

-- The below query will throw fk refernce issue. 
-- Because the dept_code PHY is not present in the dept table.
insert into students values ('1010', 'Sheldon','PHY')

// Some code
-- When the delete below query executes, it delete the record in both parent 
-- and child tables (due to on delete cascade)
delete from dept where dept_code = 'FIN'  

select * from dept
select * from students

Clean up

// clean up

drop table students
drop table dept