CODEWITHSUNDEEP
sql-serverforeign-keysprimary-keypartitioning
Ludo
Ludo

Reputation: 1

Partitioning a Table Referenced by a Foreign Key

Around 20 tables are partitioned on monthly base. They all have a date column, as the partition key. So far so good, but many of the partitioned tables are referenced by one or more foreign keys. This prevent me from doing a switch-out + drop partition. Because SQL Server give the error:

Msg 50000, Level 16, State 1, Procedure HandleError, Line 26 [Batch Start Line 0]

The table must not be referenced by a foreign key constraint.

A possibility is to drop constraints before the switch-out and to recreate them after the switch out, as you can see in the screenshot there are a lot FK’s. Another option is to execute delete statement on every impacted table before the drop of the oldest partition. But this will take time because we are talking about millions of records / partition.

In Oracle this seems to be no problem. As soon as Oracle sees that there are no references any more from the FK to the PK the partition can be dropped. In SQL server not. I tried it out just to be sure.

Does any one have a better idea how to solve this problem in an elegant way?

Picture with all partitioned tables with their reference constraints

Upvotes: 0

Views: 3182

Answers (1)

Artashes Khachatryan
Artashes Khachatryan

Reputation: 387

You can just modify your FKs not to check the existing data with nockeck constraint command. Then partition switch will work. Please see example below.

CREATE PARTITION FUNCTION part_fun_test(int)
AS RANGE RIGHT FOR VALUES(10, 20, 30)
GO
CREATE PARTITION SCHEME part_schem AS PARTITION part_fun_test
ALL TO ([primary])
GO
CREATE TABLE Main_t(
id INT IDENTITY PRIMARY KEY CLUSTERED,
name VARCHAR(50)
) ON part_schem(id)
GO

CREATE TABLE Main_t_ref(
ID INT IDENTITY, 
MainID INT CONSTRAINT FK_t FOREIGN KEY REFERENCES dbo.Main_t(id)
)
GO 
CREATE TABLE Second_t(
id INT IDENTITY PRIMARY KEY CLUSTERED,
name VARCHAR(50)
)

INSERT INTO dbo.Main_t
        ( name )
DEFAULT VALUES
GO 30 

ALTER TABLE dbo.Main_t SWITCH PARTITION 1 TO dbo.Second_t --error
GO
ALTER TABLE dbo.Main_t_ref NOCHECK CONSTRAINT FK_t
GO
ALTER TABLE dbo.Main_t SWITCH PARTITION 1 TO dbo.Second_t --no error
GO

Upvotes: 1

Related Questions