unique constraint with versioning

From: Sam Sippe <ssippe_at_yahoo.com>
Date: 19 May 2002 19:33:51 -0700
Message-ID: <8f802b4f.0205191833.58a10ac4@posting.google.com>

Hi,

Hopefully someone can give me some ideas on this one.

I'm trying to design a fairly generic table that will be able to record previous versions of an entry as well as the current one. The table will have a Primary Key Column called Id and another column called VersionPersistId along with what-ever other info I need.

The problem I'm having is trying to enforce a UNIQUE constraint on the other data in the table.

If Code is the column I want to keep unique between items and the existing data in the table is:
Id VersionPersistId Code Name

1   1                 A       Sam



...I'd like to allow:

2   1                 A       Bill
3   2                 B       Terry



...but not:

4   3                 A       Scott

Is there a good way to do this without triggers and stored procedures?

Cheers,
Sam Received on Sun May 19 2002 - 21:33:51 CDT