Problem with partitioning and foreign keys

I encountered an issue while adding partitioning to Table1.

Previously, this table had a primary key consisting only of the Id column, but when I added partitioning, I had to include AddTimestamp in the composite key. Now I want to change the structure so that the primary key consists only of Id again, but this leads to errors when trying to create a foreign key in Table2.

Problem description:

1. Partitioning: I created a partition function and partition scheme to split the data by timestamps:

CREATE PARTITION FUNCTION PF_AddTimestamp (DATETIMEOFFSET(7))
AS RANGE LEFT FOR VALUES (
    '2020-01-01T00:00:00Z',
    '2021-01-01T00:00:00Z',
    '2022-01-01T00:00:00Z',
    '2023-01-01T00:00:00Z',
    '2024-01-01T00:00:00Z'
);

CREATE PARTITION SCHEME PS_AddTimestamp
AS PARTITION PF_AddTimestamp
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

2. Creating Table1: I created the table with partitioning and a composite primary key:

CREATE TABLE Table1
(
    Id INT NOT NULL,
    AddTimestamp DATETIMEOFFSET(7) NOT NULL DEFAULT SYSDATETIMEOFFSET(),
    SomeField1 VARCHAR(100),
    SomeField2 INT,
    CONSTRAINT PK_Table1 PRIMARY KEY (Id, AddTimestamp)
)
ON PS_AddTimestamp (AddTimestamp);

However, when I tried to change the primary key to consist only of Id, I encountered the following error:

The associated partition function ‘PF_AddTimestamp’ generates more partitions than there are file groups mentioned in the scheme ‘PS_AddTimestamp’

3. Creating Table2: I created Table2 with a foreign key referencing Id from Table1:

CREATE TABLE Table2 
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Table1Id INT,
    SomeField3 VARCHAR(100),
    SomeField4 DECIMAL(10, 2),
    CONSTRAINT FK_Table2_Table1 
        FOREIGN KEY (Table1Id) REFERENCES Table1(Id)
);

But when creating the foreign key, I get the following error:

There are no primary or candidate keys in the referenced table ‘Table1’ that match the referencing column list in the foreign key ‘FK_Table2_Table1’.

I want the primary key in Table1 to consist only of Id, so that I can successfully create a foreign key in Table2 that references Table1 (Id).

How can I resolve this issue?

I would appreciate any advice or recommendations regarding this issue!

You need to sign in to view this answers