2

How could I set a constraint on a table to enforce that exactly one record per group is flagged as "primary".

E.g. in the database of a company providing risky thrill sports, they have table Customer and table EmergencyContact, where there is a FK constraint from EmergencyContact to Customer. There may be zero or more EmergencyContact records for each Customer record. If there is one or more emergency contacts for a customer, then one of them must be marked as primary. If there's exactly one emergency contact, then that's obviously the one that has to be marked as primary.

Note that a related constraint is easy to implement, and may be part of the solution - enforcing that no more than one emergency contact per customer is flagged as primary. There are already many such questions (and good answers) on stackoverflow, e.g. here, here etc. However, I haven't found an answer (or any other way) to enforce that at least one child record per group be flagged, where any child records exists.

If possible, I'd like to avoid using triggers, or constraints based on UDFs in computed columns. Triggers can get disabled, and don't always fire (see pg 208 of Alex Kuznetsov's excellent book Defensive Database Programming), and UDFs wrapped in check constraints can get bypassed or ignored in certain situations as well (see page 184 onwards of the same book).

I've tried several ways to attack the problem such as indexed views as well as a FK constraint from parent to child table, without success yet.

Reversed Engineer
  • 1,095
  • 13
  • 26
  • Note only a user with ALTER TABLE privileges can bypass trigger execution, and such a user can also disable Foreign Key and Check constraints, re-enable them checking existing rows, and disable indexes. – David Browne - Microsoft Aug 06 '18 at 18:19
  • @DavidBrowne-Microsoft - triggers that enforce constraints (like here) can also fail when using the snapshot transaction isolation level, unless special precautions are taken. (Even then, I'm not sure that they are 100% reliable). – Reversed Engineer Aug 07 '18 at 13:51
  • 1
    Absolutely agree. It's tricky to write triggers that always do what you intend, and without introducing performance issues or deadlocks. To the point where it's often better to give up on having the database enforce a particular business rule than to enforce it with a trigger. – David Browne - Microsoft Aug 07 '18 at 14:00
  • @DavidBrowne-Microsoft Triggers sometimes just fail when they run under snapshot isolation. Please see Alex Kuznetsov's excellent book, [Defensive Database Programming with SQL Server](http://assets.red-gate.com/community/books/defensive-database-programming.pdf), from page 114 onwards, and especially the subheading Trigger behavior in SNAPSHOT mode on page 122. ([alternative download link](https://web.archive.org/web/20180403094624/http://download.red-gate.com/ebooks/SQL/defensive-database-programming.pdf)) – Reversed Engineer Feb 14 '20 at 09:55
  • Quote from _Defensive Database Programming with SQL Server_: "Could we have developed more robust triggers; ones that continue to enforce our business rules under snapshot isolation? In this case, **I do not think so.**" – Reversed Engineer Feb 14 '20 at 09:56

1 Answers1

2

Personally the way I would handle this is with a CHECK constraint.

If you must avoid CHECK constraints and triggers, your next best option is to set permissions on the table such that adhoc INSERTs and UPDATEs are denied and the only way to change the data in the table is through a stored procedure. Then put the logic you describe in the stored procedure code.

You're not going to find a way to do this with indexes or foreign keys.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52