patternsqlModerate
Having a unique ID for 2 different tables
Viewed 0 times
uniquetableshavingdifferentfor
Problem
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.
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.
Solution
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)
)Code Snippets
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)
)Context
StackExchange Database Administrators Q#12715, answer score: 12
Revisions (0)
No revisions yet.