T-SQL Unique Constraint WHERE AnotherColumn = ParticularValue

Question

Consider a link table with the following columns:

PersonID int NOT NULL
LocationID int NOT NULL
Active bit NOT NULL
...

The system allows each Person and Location to be configured independently. Once configured, each Person can be linked to at most one Location. If a Person moves to a new Location, the link is to be deactivated, not deleted, so that the system knows when the Person was last linked to a particular Location. A Person can have any number of inactive links, but at most one active link. A Location can have any number of Persons actively linked to it.

How would I add a constraint to this table to prevent a second active link from being created for a Person when one already exists?

Edit: I thought this was a 2008 box... turns out it's 2005, so filtered indexes won't work.

possible duplicate of [Constraint for only one record marked as default.](http://stackoverflow.com/questions/637894/constraint-for-only-one-record-marked-as-default) — Damien_The_Unbeliever, Nov 04 '10 at 14:24
Or http://stackoverflow.com/questions/3997837/unique-constraint-controlled-by-a-bit-column/3997883#3997883 — Damien_The_Unbeliever, Nov 04 '10 at 14:28
if you look at the answers on the questions I've posted, you'll see that you can do a "poor man's" filtered index on earlier versions, using an indexed view. — Damien_The_Unbeliever, Nov 05 '10 at 08:10

score 3 · Answer 1 · answered Nov 04 '10 at 14:28

3

Rather than a linking table, have a link to Location ID on the Person table, as SilverSkin suggests. Now, if you want a list of inactives, add a trigger to the Person table to insert into a history table (a modification of your linking table) every time the location is changed (<> the last history table entry / one does not exist for the person). The link in the Person table gives the active link, while the history table gives the history and (should a person ping-pong between locations) an indication of the history of locations rather than a list of inactives.

answered Nov 04 '10 at 14:28

jrnewman42

108
7

I like your idea. Unfortunately, I'm stuck with the schema I've got. :) – Calvin Fisher Nov 04 '10 at 14:30
More's the pity. I like it, too. :) – jrnewman42 Nov 04 '10 at 15:32

score 3 · Accepted Answer · answered Nov 10 '10 at 08:27

Using an indexed view to implement a "filtered index" on versions of SQL Server predating 2008:

CREATE VIEW vOnlyOneActive
AS
  SELECT PersonID
  FROM <underlying table>
  WHERE Active = 1
GO
CREATE UNIQUE CLUSTERED INDEX IX_vOnlyOneActive on vOnlyOneActive (PersonID)
GO

You'll need to have the right ANSI settings turned on for this.

score 2 · Answer 3 · answered Nov 04 '10 at 14:12

2

Constraints do not work across multiple rows, so you will need an INSERT/UPDATE trigger to enforce only one active record for a group of records with the same PersonID and LocationID.

answered Nov 04 '10 at 14:12

D'Arcy Rittich

167,292
40
290
283

I've not used triggers before... what/what kind of trigger would be best to use here? – Calvin Fisher Nov 04 '10 at 14:25
When you write that trigger make sure you make it so it can handle multiple row inserts or deltes, triggers do NOT process one row at a time. – HLGEM Nov 04 '10 at 15:14

score 0 · Answer 4 · answered Nov 04 '10 at 14:12

0

foreign key references from tblPerson to PersonID, from tblLocation to LocationID, and a joint key on PersonId and LocationId to make sure that they are unique together.

answered Nov 04 '10 at 14:12

Kalle

2,282
1
24
30

1

With just that, there could still be two rows with the same PersonID, different LocationIDs, and both Active = 1, which should not be allowed. – Calvin Fisher Nov 04 '10 at 14:13
True. Then you might consider adding the locationId and active bit to the person table, which only allows one location per person... – Kalle Nov 04 '10 at 14:16
That would do the constraining job. But then the system would not have a record of the last time a Person was linked to a Location, which is also a requirement. :) – Calvin Fisher Nov 04 '10 at 14:20
That depends, if you set locationActive = 0 on a person, you would still have the location info until the next time. Ah, I see, you still want to save the inactive links? Right, gotcha. – Kalle Nov 04 '10 at 14:22

T-SQL Unique Constraint WHERE AnotherColumn = ParticularValue

4 Answers4

Linked