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
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
create unique index unq_bookid_issue on (BookId, computed_bookissueid);