unique constraint with versioning

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 Mon May 20 2002 - 04:33:51 CEST