Unique Index

[expired user #8933]'s profile image [expired user #8933] posted 9 years ago in General Permalink
Hi,

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.

Please login to leave a reply, or register at first.