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:
- 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]);
- 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’
- Creating
Table2
: I createdTable2
with a foreign key referencingId
fromTable1
:
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