drop table if exists dbo.t2;
drop table if exists dbo.t1;
go

create table dbo.t1 (id int primary key);
go
create table dbo.t2 (pk_id int, val varchar(255), constraint fk foreign key (pk_id) references dbo.t1(id) on delete set null)
go
insert into dbo.t1 values (1), (2) ,(3)
go
insert into dbo.t2 values (1, 'TEST'), (1, 'TEST'), (2, 'TEST2'), (3, 'TEST3')
go
begin try
insert into dbo.t2 values (4, 'TEST')
end try
begin catch
select error_number(), error_message()
end catch
go
select delete_referential_action_desc, * from sys.foreign_keys where parent_object_id = object_id('dbo.t2') --SET_NULL
go

begin tran
delete from dbo.t1 where id = 3
select * from dbo.t2 --запись с pk_id=3 стала NULL
rollback;
go

--пересоздаем fk
alter table dbo.t2 
drop constraint fk
go

alter table dbo.t2 
add constraint fk foreign key (pk_id) references dbo.t1(id)
go
select delete_referential_action_desc, * from sys.foreign_keys where parent_object_id = object_id('dbo.t2') --NO_ACTION
go
--//пересоздаем fk

begin tran
begin try
delete from dbo.t1 where id = 3
end try
begin catch
select error_number(), error_message()
select * from dbo.t2 --запись с pk_id=3 осталась нетронутой
end catch
rollback;
go

drop table if exists dbo.t2;
drop table if exists dbo.t1;
go