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

Question

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#

Please tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2016-11-19 12:15:49 +00:00
Commented Nov 19, 2016 at 12:15 — Gordon Linoff
– Gordon Linoff, Commented Nov 19, 2016 at 12:15

Gordon Linoff · Accepted Answer · 2016-11-19 14:42:07Z

1

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

edited Nov 19, 2016 at 14:42

answered Nov 19, 2016 at 12:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jitender Over a year ago

thanks that's work with a little change in syntax create unique index unq_bookissuedReference on Books(book_id) where [status] = 'issued'

Sefe · Accepted Answer · 2016-11-19 11:57:21Z

1

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

edited Nov 19, 2016 at 11:57

answered Nov 19, 2016 at 11:48

Sefe

14.1k6 gold badges45 silver badges56 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:24:47Z

0

Using Gordon answer

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

Works for me

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Nov 19, 2016 at 13:03

jitender

10.4k1 gold badge22 silver badges49 bronze badges

2 Comments

Sefe Over a year ago

Anything that works. If your unique index gets too big you can still switch to the check constraint, which will not increase DB size.

jitender Over a year ago

@safe I also tried your solution too and it also works fine

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related