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