Having a unique ID for 2 different tables We would like to use two different tables, one will hold an object when it is 'Active', and the other will hold the object once it becomes'Non-Active'. 
The ID is therefore unique per both tables (combined). 
How can we create a constraint for the ID such that the ID appears only once for both tables?

We are using SQL Server.
This should do it I think.

CREATE TABLE Foo 
(
FooId INT PRIMARY KEY,
Active BIT NOT NULL,
UNIQUE(FooId, Active)
)

CREATE TABLE FooActive 
(
FooId INT PRIMARY KEY,
Active AS CAST(1 AS BIT) PERSISTED,
FOREIGN KEY (FooId, Active) REFERENCES Foo(FooId, Active)
)

CREATE TABLE FooInActive 
(
FooId INT PRIMARY KEY,
Active AS CAST(0 AS BIT) PERSISTED,
FOREIGN KEY (FooId, Active) REFERENCES Foo(FooId, Active)
)