CODEWITHSUNDEEP
sql-server
ArcherBird
ArcherBird

Reputation: 2134

SQL Server: Uniqueness for only a specific value

I'm struggling a little bit with how to word this question. Edits are welcomed:

I have a SQL Server table (PlanID, PlanDesc, PlanType) where the PlanID is the PK. For the column PlanType - I want this to have the property where Only 1 PlanID can be assigned the PlanType value "AP". BUT Many PlanIDs are allowed to be assigned any other PlanType values.

So something like this would be valid:

INSERT INTO MyTable VALUES (1, 'Plan A', OP)
INSERT INTO MyTable VALUES (2, 'Plan B', OP)
INSERT INTO MyTable VALUES (3, 'Plan C', AP)
INSERT INTO MyTable VALUES (4, 'Plan D', LP)
INSERT INTO MyTable VALUES (5, 'Plan E', LP)

But as soon as I try to insert (or update) any other plan with PlanType AP, it would fail if an existing plan already has the PlanType AP.

Is there any way to accomplish this type of constraint in the DB structure. Or is my only choice to carefully code this logic into the application?

Upvotes: 1

Views: 42

Answers (2)

user330315
user330315

Reputation:

You can create a filtered index:

create unique index only_one_plantype on MyTable (plantype) 
where plantype = 'AP';

Upvotes: 3

Cam
Cam

Reputation: 2188

You can create a trigger on the table to do this:

CREATE TRIGGER tr_MyTable_AP
ON MyTable FOR INSERT
AS
    IF (SELECT COUNT(*) FROM MyTable WHERE PlanType = 'AP') > 1
        ROLLBACK TRANSACTION

If you attempt to insert a row with PlanType 'AP' and there is already one in the database you'll end up with:

Msg 3609, Level 16, State 1, Line 35
The transaction ended in the trigger. The batch has been aborted.

Hope this helps.

Upvotes: 1

Related Questions