Suppose I have the following
table a
serial_no varchar(8)
serialised bit
table b:
serial_no varchar(8) (foreign key)
return_date datetime null
Can I create a unique index that does not allow duplicate records by serial_no and a null return_date, ONLY IF the serialised field from table a is true.
Something like:
create unique index unq_loan_serial_id
on table b(serial_no) where return_date is null and table a.serialised = 1
Any ideas on how I can do this? Thanks
You cannot apply this directly to the table - but you could create an indexed view.
Something like:
CREATE VIEW dbo.b_serialised --Guessing everything in dbo schema
WITH SCHEMABINDING
AS
SELECT
b.Serial_No
FROM
dbo.a
inner join
dbo.b
on
a.Serial_No = b.Serial_No --Guessing this was the implied join
WHERE
b.return_date is null and
a.serialised = 1
Followed by
create unique clustered index UQ_b_serialised on b_serialised (Serial_No)