SQL Server Partitioning - Unique Index Error
Or you create NON-partitioned index, or you HAVE to include the partitioning key into partitioned index like this:
Partitioned index
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_ON_PS_DATETIME] ON [CRD].[TRANSACTION]
(
[ID] ASC,
TRANSACTION_DATE_TIME
) ON [PS_DATETIME_WEEKLY]([TRANSACTION_DATE_TIME])
OR
Non-partitioned index
CREATE UNIQUE NONCLUSTERED INDEX [IX_ID_ON_PS_DATETIME] ON [CRD].[TRANSACTION]
(
[ID] ASC
) ON PRIMARY
From the TechNet Microsoft page
When partitioning a unique index (clustered or nonclustered), the partitioning column must be chosen from among those used in the unique index key. If it is not possible for the partitioning column to be included in the unique key, you must use a DML trigger instead to enforce uniqueness.
So to implement a workaround you can check this link out... one workaround from the blog and another one in the comments
Dealing with Unique Columns when Using Table Partitioning