Edit - Stack Overflow

AI Assist is now on Stack Overflow. Start a chat to get instant answers from across the network. Sign up to save and share your chats.

1. Home
2. Questions
3. AI Assist
4. Tags
6. Challenges
7. Chat
8. Articles
9. Users
11. Jobs
12. Companies
13. Collectives
14. Communities for your favorite technologies. Explore all Collectives
Stack Internal

Stack Overflow for Teams is now called Stack Internal. Bring the best of human thought and AI automation together at your work.
Try for free Learn more
Stack Internal
Bring the best of human thought and AI automation together at your work. Learn more

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

Rev

Required fields*

conditional unique constraint

I have a situation where i need to enforce a unique constraint on a set of columns, but only for one value of a column.

So for example I have a table like Table(ID, Name, RecordStatus).

RecordStatus can only have a value 1 or 2 (active or deleted), and I want to create a unique constraint on (ID, RecordStatus) only when RecordStatus = 1, since I don't care if there are multiple deleted records with the same ID.

Apart from writing triggers, can I do that?

I am using SQL Server 2005.

Answer*

Add a check constraint like this.  The difference is, you'll return false if Status = 1 and Count > 0.

http://msdn.microsoft.com/en-us/library/ms188258.aspx


    CREATE TABLE CheckConstraint
    (
      Id TINYINT,
      Name VARCHAR(50),
      RecordStatus TINYINT
    )
    GO
    
    CREATE FUNCTION CheckActiveCount(
      @Id INT
    ) RETURNS INT AS BEGIN
      
      DECLARE @ret INT;
      SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
      RETURN @ret;
    
    END;
    GO
    
    ALTER TABLE CheckConstraint
      ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1));
    
    INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
    INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
    INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
    INSERT INTO CheckConstraint VALUES (1, 'No Problems', 1);
    
    INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
    INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);
    -- Msg 547, Level 16, State 0, Line 14
    -- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint".
    INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
    
    SELECT * FROM CheckConstraint;
    -- Id   Name         RecordStatus
    -- ---- ------------ ------------
    -- 1    No Problems  2
    -- 1    No Problems  2
    -- 1    No Problems  2
    -- 1    No Problems  1
    -- 2    Oh no!       1
    -- 2    Oh no!       2
    
    ALTER TABLE CheckConstraint
      DROP CONSTRAINT CheckActiveCountConstraint;
    
    DROP FUNCTION CheckActiveCount;
    DROP TABLE CheckConstraint;

Edit Summary*

Cancel

i looked at table level check constraints but doesnt look there is any way to pass the values being inserted or updated to the function, do you know how to ?

np-hard
– np-hard

2009-05-14 22:11:05 +00:00
Commented May 14, 2009 at 22:11
Okay, I posted a sample script that will help you prove what I'm talking about. I tested it and it works. If you look at the two commented lines, you'll see the message I get. Nota bene, in my implementation, I merely ensure that you cannot add a second item with the same Id which is active if there is already one active one. You could modify the logic such that if there is an active one, you cannot add any item with the same id. With this pattern, the possibilities are pretty much endless.

D. Patrick
– D. Patrick

2009-05-14 22:24:00 +00:00
Commented May 14, 2009 at 22:24
I'd prefer the same logic in a trigger. "a query in a scalar function... can create big problems if your CHECK constraint relies on a query and if more than one row is affected by any update. What happens is that the constraint gets checked once for each row before the statement completes. That means statement atomicity is broken and the function will be exposed to the database in an inconsistent state. The results are unpredicable and inaccurate." See: blogs.conchango.com/davidportas/archive/2007/02/19/…

onedaywhen
– onedaywhen

2009-05-15 08:21:43 +00:00
Commented May 15, 2009 at 8:21
That's only partially true onedaywhen. The database behaves consistently and predictably. The check constraint will execute after the row is added to the table and before the transaction is committed by the dbms and you can count on that. That blog was talking about a pretty unique problem where you need to execute the constraint against a set of inserts rather than just one insert at a time. ashish is asking for a constraint on one insert at a time and this constraint will work accurately, predictably, and consistently. I'm sorry if this sounded terse; I was running out of characters.

D. Patrick
– D. Patrick

2009-05-15 13:34:37 +00:00
Commented May 15, 2009 at 13:34
3

This works great for inserts but doesn't seem to work for updates. E.G. Adding this after the other inserts works here when I didn't expect it to. INSERT INTO CheckConstraint VALUES (1, 'No ProblemsA', 2); update CheckConstraint set Recordstatus=1 where name = 'No ProblemsA'

dwidel
– dwidel

2009-08-24 17:13:16 +00:00
Commented Aug 24, 2009 at 17:13

| Show 2 more comments

Correct minor typos or mistakes
Clarify meaning without changing it
Add related resources or links
Always respect the author’s intent
Don’t use edits to reply to the author

create code fences with backticks ` or tildes ~
```
like so
```
add language identifier to highlight code
```python
def function(foo):
print(foo)
```
put returns between paragraphs
for linebreak add 2 spaces at end
_italic_ or **bold**
indent code by 4 spaces
backtick escapes `like _so_`
quote by placing > at start of line
to make links (use https whenever possible)

<https://example.com>

[example](https://example.com)

<a href="/api/flow.js?q=https%3A%2F%2Fexample.com">example</a>

formatting help »
answering help »