5

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.

2
  • 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?
    – jamesbtate
    Feb 12, 2012 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, 2012 at 8:14

2 Answers 2

12

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)
)
2
  • @MartinSmith - Hi Martin, thank you for your answer. Can you please provide your input on gbn's answer below, about using a view in such a case?
    – Shani
    Feb 14, 2012 at 10:04
  • @Shani - I would probably either have Active and InActive in the same (possibly partitioned) table or if I did decide to archive InActive ones in a different table (which might have some advantages in different indexing strategies) I probably wouldn't bother with the constraint. Simply because moving them from one state to another will be a pain. The supertype/subtype pattern is more useful when the subtypes have different attributes. Feb 14, 2012 at 10:34
4

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

4
  • 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, 2012 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, 2012 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, 2012 at 14:41
  • 1
    @Shani: It isn't recommended. It's an alternative to avoid having 2 tables. It is that simple
    – gbn
    Feb 15, 2012 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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