Partitioning with primary and foreign key relationships

Hi Mates,

This post is just my observations and I strongly recommends not to follow on your production Servers.

Well, we have a scenario where we need to partition the table which has primary and foreign key relationship to other tables. You might have a question what is the problem if they are present?

when a table is partitioned and referenced by a foreign key, you can't alter the table and switch the partition to another table.

To test this I made use of Worldwideimporters Database and made use of sales.orders table.

Below is the partition function  and scheme what I created

USE [WideWorldImporters]

/****** Object:  PartitionFunction [funtest]    Script Date: 27/05/2020 11:12:22 ******/
CREATE PARTITION FUNCTION [funtest](int) AS RANGE RIGHT FOR VALUES (20000, 40000, 60000)

/****** Object:  PartitionScheme [funcscheme]    Script Date: 27/05/2020 11:12:57 ******/

if you see the table sales.orders below are the constraints that are present on them.

By Default there was primary key created on Order ID column however I made the partition  by using drop_existing hence it is created now on  my partition scheme. This is how it looks after 

Let's imagine now I need to switch the partition 1 to another staging table so how does we gonna do this?

create the staging table and place it on same partition scheme to that of source table and if you look at the above picture it has Zero rows for all the 4 partitions. 

By the by I have not created any constraints on my staging table it just has primary key.

Now let's try to perform switch and see what error will we get

alter table sales.orders switch partition 1 to sales.orders_load partition 1

Msg 7733, Level 16, State 4, Line 31
'ALTER TABLE SWITCH' statement failed. The table 'WideWorldImporters.Sales.orders' is partitioned while index 'FK_Sales_Orders_CustomerID' is not partitioned.

As you know from my previous post we need to disable the non clustered indexes on the source table let's do that first. Then I ran the switch statement this time it got failed with 

Msg 4967, Level 16, State 1, Line 31
ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'WideWorldImporters.Sales.orders' contains primary key for constraint 'FK_Sales_OrderLines_OrderID_Sales_Orders'.

Now Let's disable all the foreign keys on the table with the below statement. 

alter table sales.orders nocheck constraint all

As you can see all the Foreign keys are in Disabled state.  Also the keys are now not trusted

Let's trigger the switch statement now and see what happens?

Msg 4967, Level 16, State 1, Line 31
ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'WideWorldImporters.Sales.orders' contains primary key for constraint 'FK_Sales_OrderLines_OrderID_Sales_Orders'.

Wow it failed again as the table is being referenced by 2 child tables sales.invoices and sales.orderlines so we will proceed with disabling them to.

alter table sales.orderlines nocheck constraint FK_Sales_OrderLines_OrderID_Sales_Orders
alter table sales.invoices nocheck constraint FK_Sales_Invoices_OrderID_Sales_Orders

After doing this lets try to switch again. This time we were able to do this as you can see from the below image the  load table has 19999 rows

okay lets try to enable the constraints with the below statement . I didn't encounter any errors

alter table sales.orders with check  check constraint all 

Now lets try to enable the individual constraints which are being referenced by other tables. Boom there you go.

Msg 547, Level 16, State 0, Line 65
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Sales_OrderLines_OrderID_Sales_Orders". The conflict occurred in database "WideWorldImporters", table "Sales.Orders", column 'OrderID'.

Let's check the no of rows inside the tables sales.orders (Parent) and sales.orderlines(child)

 select  distinct OrderID from sales.orders where OrderID<25 order by OrderID

This has Zero rows

select  distinct OrderID from sales.OrderLines where OrderID<7 order by OrderID

This has 6 rows which means the records were deleted from the parent but not from the child. 

As said in the beginning of the post partitioning switch will not work if the table has foreign keys which are being referenced with other tables

You can't do a switch out if there's another table which is dependent on the switching table with a Foreign Key

Imagine that you have PK values 1,2,3, and a foreign key into it. Now you want to switch out value 3, so it'll have to check that 3 isn't mentioned in the Foreign Key table - that's a large amount of work, and a lot more than the metadata operation that partition switching is meant to be.