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
.