Sql-server – Composite Primary Key on partitioned tables, and Foreign Keys

database-designforeign keypartitioningprimary-keysql server

We have a database where several tables are partitioned. The primary key (PK for brevity) for these tables is of the form

  • transaction_date, id

Where transaction_date is the partitioning key, and id is a unique integer column. If we were not partitioning the table, then we would only use id as the PK.

We have several other tables which need foreign keys (FKs) to reference these partitioned tables. I cannot decide whether to use composite FKs, referencing both columns of the PK, or single column FKs, referencing the id column supported by a unique index.

Are there any advantages/disadvantages of either approach? So far the only factors I can think of (and I may even be wrong on these) are:

  • Single column FK: Reduces app development complexity.

  • Composite FK: Allows partition elimination when joining on FK columns.

Best Answer

ALL unique constraints involved in a partitioning scenario MUST have the partition column defined as part of the base index definition (subset of the index key) - (not merely an included column). In other words, you will not be able to create a unique constraint on just the ID column in this scenario.

Set up a partition function and scheme.

CREATE PARTITION FUNCTION [PF_dbo_Test] (DATE) AS RANGE RIGHT
FOR VALUES ('2018-01-01','2018-02-01','2081-03-01')
GO

CREATE PARTITION SCHEME [PS_dbo_Test] AS PARTITION [PF_dbo_test] 
TO ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])

Create a table with a PRIMARY KEY that references the partition scheme.

IF OBJECT_ID('[dbo].[Test]') IS NOT NULL
    DROP TABLE [dbo].[Test]
GO

CREATE TABLE [dbo].[Test] (
    [TransactionDate] DATE
    ,[ID] INT CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TransactionDate] ASC) 
    ) ON PS_dbo_Test (TransactionDate)

Now, try to create a unique constraint on the partitioned table without specifying the partition column.

CREATE UNIQUE NONCLUSTERED INDEX [UX_Test] ON [dbo].[Test] ([ID] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            )

Msg 1908, Level 16, State 1, Line 17 Column 'TransactionDate' is partitioning column of the index 'UX_Test'. Partition columns for a unique index must be a subset of the index key.

So, when you ask the question:

I cannot decide whether to use composite FKs, referencing both columns of the PK, or single column FKs, referencing the id column supported by a unique index.

I'm afraid SQL Server has decided for you. You will have to define your FK's with both TransactionDate AND ID.

Related Question