/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

Referential Integrity
2011-01-20
*****************************************************/
set nocount on
go

create table dbo.Orders
(
	OrderId int not null identity(1,1),
	Placeholder char(100) not null
		constraint Def_Orders_Placeholder
		default 'a',
			
	constraint PK_Orders
	primary key clustered(OrderId)
)
go

create table dbo.OrderItems
(
	OrderItemId int not null identity(1,1),
	OrderId int not null,
	Placeholder char(100) not null
		constraint Def_OrderItems_Placeholder
		default 'a',
			
	-- in real life use clustered index on (OrderId, OrderItemId)
	constraint PK_OrderItems
	primary key clustered(OrderItemId)
)
go
	
set identity_insert dbo.Orders on
;with CTE(Num)
as
(
	select 1

	union all

	select Num + 1
	from CTE 
	where Num < 1000
)
insert into dbo.Orders(OrderId)
	select Num from CTE
option (MAXRECURSION 0)
set identity_insert dbo.Orders off
go

alter table dbo.OrderItems
with check
add constraint FK_OrderItems_Orders
foreign key(OrderId)
references dbo.Orders(OrderId)
on update cascade
on delete cascade
go

-- Enable display execution plan

-- introduce additional CI seek on the Orders table
insert into dbo.OrderItems(OrderId)
values(1)
go

-- CI SCAN on OrderItems table
delete from dbo.Orders
where OrderId = 1
go

create index IDX_OrderItems_OrderId
on dbo.OrderItems(OrderId)
go

-- NCI SEEK on OrderItems table
delete from dbo.Orders
where OrderId = 2
go

