sqlvogel (1/4/2012)
GSquared (1/4/2012)
For each pseudo-ID, there was only ever supposed to be one row marked active, and all the rest were to have a 0 in that column.How would you enforce that in SQL Server? I'm pretty sure it can't be done reliably with CHECK constraints. Indexed views and filtered unique indexes won't stop you updating all rows to be Active=0. Would you use triggers?
All rows set to 0 = row deleted, in this kind of schema. Nothing wrong with that. Sorry for the ambiguity in the statement. Should have been:
For each pseudo-ID, there was only ever supposed to be one row marked active, and all the rest were to have a 0 in that column, or all rows marked 0 for "deleted" data.
That's kind of mentioned later in my post, but not clearly enough.
Filtered unique index was the solution I recommended to them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon