CODEWITHSUNDEEP
c#sqllinq-to-sql
jitender
jitender

Reputation: 10429

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#

Upvotes: 0

Views: 315

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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
    where status = 'issued' ;

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);

Upvotes: 1

jitender
jitender

Reputation: 10429

Using Gordon answer

create unique index unq_bookissuedReference
on Books(Book_id) where [status] = 'issued'

Works for me

Upvotes: 0

Sefe
Sefe

Reputation: 14007

You have a complex condition here, so a UNIQUEconstraint won't help you. You will need a CHECKconstraint.

You first need a function to to do your check:

CREATE FUNCTION dbo.IsReturnDuplicate
(
    @id INT,
    @bookid INT,
    @status VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
    RETURN (SELECT TOP 1 COUNT (*) FROM bookreturns WHERE (id <> @id) AND (status = @status) AND (bookid = @bookid) AND (status = 'issue')
END

This will return 1 if there is already a row in the table that has status 'issue' and has a different id

You can then create a CHECK constraint using this function

CREATE TABLE bookreturns (
    --...
    CONSTRAINT CK_bookreturns_status CHECK (dbo.IsReturnDuplicate(id, bookid, status) == 0)
)

Upvotes: 1

Related Questions