jitender jitender - 3 months ago 19
C# Question

Sql or linq to sql Unique Constraint based on two columns value

I don't know is it possible or not.
I have a table that keeps records for a book issue return.There are two columns in this one is [status] and other [bookid] .I want to add a constraint in sql that will restrict user to insert duplicate record for status="issue" with same bookid.

For example if there is already a record with status='issue' and bookid=1 then it must not allow to insert other record with status="issue" and bookid=1 but there can me multipule records with other status like staus='return' and bookid=1 may occur a number of times.

Or there may be solution using linq to sql in c#


You do not need a user defined function, in general. In SQL Server (and many other databases) you can just use a filtered index:

create unique index unq_bookissue
    on (bookid,
        (case when status = 'issue' then -1 else bookissueid end)

In this case, bookissueid is a unique identifier for each row in the table.

Actually, SQL Server allows NULLs to be duplicated in a unique index (this varies by database), so you don't need the unique column:

create unique index unq_bookissue
    on (bookid,
        (case when status = 'issue' then 'issue' end)

However, I don't like depending on how NULL is treated in unique indexes, because this varies by database.

In earlier versions of SQL Server you can do this with a computed column, assuming that you have a table with columns such as:

  • BookId, which is repeated across rows.
  • Status, which should be unique when the value is issue.
  • BookIssueId, which uniquely identifies each row.

Then, add a computed column to keep track of status = 'issue':

alter table t add computed_bookissueid as (case when status = 'issue' then -1 else BookIssueId end);

Now add a unique index on this column and BookId:

create unique index unq_bookid_issue on (BookId, computed_bookissueid);