Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question
2  
Label me naive, but why wouldn't you just add a field to the table to denote active/non-active? Aren't you inventing problems for yourself by doing it this way? – Puddingfox Feb 12 '12 at 21:55
1  
We have many 'SELECT' queries on the Active objects table, and we would like to maximize the queries performance. These tables are dynamic, and large in size. We would love to here other suggestions. Maybe I should open a new thread on this issue? – Shani Feb 13 '12 at 8:14

2 Answers

up vote 10 down vote accepted

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)
)
share|improve this answer
1  
@a1ex07 - But then Foo can have the same FooId twice. Once active and once inactive. The PK is needed to avoid that and the Unique Constraint for the FK to point to. – Martin Smith Feb 12 '12 at 14:35
1  
@a1ex07 - IDENTITY does not guarantee uniqueness. CREATE TABLE Foo2(FooId INT IDENTITY,Active BIT);SET IDENTITY_INSERT Foo2 ON;INSERT INTO Foo2(FooId,Active) VALUES (1,0),(1,1);SET IDENTITY_INSERT Foo2 OFF; – Martin Smith Feb 12 '12 at 15:14
3  
@a1ex07 - Agree it is a shame that SQL Server implements the (redundant) logical unique constraint on (FooId, Active) by creating a unique index as this is already guaranteed by the PK constraint on FooId. Would be nice if you could just create a unique index on Foo(FooId) INCLUDE (Active) and not have the auto generated indexes as this guarantees both logical constraints. Still prefer declarative integrity to code in triggers though as this is difficult to get correct and often inefficient. – Martin Smith Feb 12 '12 at 15:25
1  
P.S. Your tables are a bit FU'ed... – ErikE Feb 12 '12 at 19:55
1  
@ErikE - Doh! Can't believe I missed that. – Martin Smith Feb 13 '12 at 9:10
show 10 more comments
up vote 4 down vote

In addition to Martin's answer (which is the superkey/subtype pattern), I'll ask the obvious...

Why not use a single table and two views?

I wouldn't normally consider two separate tables... two partitions, maybe, if I had bazillions of rows and proved it adds some value. But this sounds like an unnecessary optimisation or pointless complexity. Not least, rows have to be moved between tables

share|improve this answer
I have been reading about DB views, and understand that they are virtual presentation of existing tables. I don't understand when is the virtual table formed and how can this help us performance-wise? Is it formed when a query of the view is performed? Or is this table updated once the table within the view is updated? – Shani Feb 14 '12 at 10:04
A view always shows the latest data in the table. It can never be out of date, also it isn't really created or formed because it is just a macro. Performance wise, you'd have to test. But I don't think you need 2 tables... – gbn Feb 14 '12 at 10:06
Hi gbn, I have read some more on the subject and 'played' a little with tables and views. I still don't understand why a view would be better than querying directly from a table (let's say all the data is within one table); the execution plans are similar. Most posts on Views explain the advantages security wise and aggregation wise, and for report creation. Can you please elaborate some more on why a view is recommended in this case? Thanks – Shani Feb 15 '12 at 14:41
@Shani: It isn't recommended. It's an alternative to avoid having 2 tables. It is that simple – gbn Feb 15 '12 at 15:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.