1

I have a table that has a date field for the release date of a drawing. The release date is tied to the drawing number from the table of drawings. A drawing can not be released twice on the same date, so I set a constraint to allows only one ReleaseDate + DwgKeyNum. There is also a void field and a unique PK: 

  UKey (PK, int, not null)
  ReleaseDate (date, not null)
  ReleaseDescription (nvarchar(80), null)
  DwgKeyNum (FK, int, not null)
  VoidOn (bit, not null)
  Updated (datetime, null)

In my app, if you make a mistake in this table, you can correct it UNLESS the data is over 2 months old. In that case, you must VOID the record and create a new one.

Of course, because of my constraint, the new record can not use the same date as the voided record (oops). So, how do I fix that? I could add the void field to the constraint, but that only allows one more record. I want to allow one record where VoidOn is False, and "n" records where VoidOn is True.

I read question 767657 and I think the solution to this problem is to create a separate unique identifier that has a null value if VoidOn is True. Seems awkward if they then UNvoid the record (which I allow), as I would need to reset the null identifier somehow. Is there a better approach?

I spent my youth learning Dataperfect which allowed "exception lists" for each index (fields that could be used to remove a record from a given index). This worked great since you could include/exclude a record from an index instantly. Not sure how best to accomplish in SQL.

Community
  • 1
  • 1
Matt D
  • 73
  • 11
  • 3
    you could consider using a unique index (like `create unique nonclustered index IX_myTable_RDate_KeyNum_Void on myTable(ReleaseDate, DwgKeyNum) WHERE VoidOn <> 1` or something like that instead of a unique constraint). EDIT: just read the question you linked - the top answer that isn't marked as answer is basically the idea you want except instead of "not null" you just want " = 0" or "<> 1" – ZLK Aug 22 '16 at 22:41

0 Answers0