I am trying to create an unique index on the following SQL Server table that stores relationship information between two persons with constraints where
[size="1"][end_date] IS NULL OR [end_date] >= GETDATE()[/size]
CREATE TABLE [dbo].[person_relationship](
[person_relationship_id] [bigint] IDENTITY(1,1) NOT NULL,
[person_id_1] [bigint] NOT NULL,
[person_id_2] [bigint] NOT NULL,
[person_relationship_type_id] [bigint] NOT NULL,
[start_date] [datetime] NOT NULL,
[end_date] [datetime] NULL,
CONSTRAINT [PK_person_relationship] PRIMARY KEY CLUSTERED
(
[person_relationship_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[person_relationship] WITH CHECK ADD CONSTRAINT [FK_person_relationship_1_person] FOREIGN KEY([person_id_1])
REFERENCES [dbo].[person] ([person_id])
GO
ALTER TABLE [dbo].[person_relationship] CHECK CONSTRAINT [FK_person_relationship_1_person]
GO
ALTER TABLE [dbo].[person_relationship] WITH CHECK ADD CONSTRAINT [FK_person_relationship_2_person] FOREIGN KEY([person_id_2])
REFERENCES [dbo].[person] ([person_id])
GO
ALTER TABLE [dbo].[person_relationship] CHECK CONSTRAINT [FK_person_relationship_2_person]
GO
The following is the unique index I am attempting to create but SQL is not having it.
CREATE UNIQUE INDEX [IX_person_1_person_2_relationship_type]
ON [dbo].[person_relationship]([person_id_1], [person_id_2], [person_relationship_type_id])
WHERE ([end_date] IS NULL) OR ([end_date] >= GETDATE())
The issues are "OR" and "GETDATE()". I found out that using date functions result in error but not sure why "OR" is causing grief.
Any advise/suggestions will be deeply appreciated.