I have a table where all actions affect single rows (insert and update, no delete's but if there were, they would be individual as well). I'm considering using a UDF in a check constraint so I can make sure that a field value is only used in combination with another value (two FK's, the first time A=1 is used, B=x becomes fixed, so that if x = 4 then A=1 can never be matched with B=3 or anything except 4).

What are the downsides of doing this, other than the neligible additional time spent when inserting/updating the row?

UPDATE: The application doesn't provide a mechanism for changing either column A or B after the row has been created.

  • 1
    Personally I find an instead of trigger a more usable way to deal with constraints that involve more than the row being inserted. The primary reason is that instead of reacting to an insert and then undoing it, you can simply prevent it from happening in the first place, and only proceed when your business rules haven't been violated. YMMV. – Aaron Bertrand Aug 10 '12 at 16:33
  • In general, UDFs in check constraints are extremely slow and unreliable: they can return both false positives and false negatives. If you describe your problem in more detail, we may come up with a better solution. – A-K Aug 10 '12 at 16:44
  • 2
    I'm merely comparing an instead of trigger to the typical approach - an after trigger - which allows the row to be inserted, then rolls back. I don't know the internals of a check constraint well enough to know exactly how far into the process the rejection happens, but I do know that the thought of combining a check constraint with a UDF that will compare that row to the rest of the table does not "feel" right to me. – Aaron Bertrand Aug 10 '12 at 17:17
  • 1
    I would prefer this type of data integrity handling to be performed in the application before making it to the databae. Letting the update take place, then checking the data to see if it breaks the rules, rolling back if it does committing if it does not...while it is a valid way of doing it you are opening the data up to the ever popular turbo button "with no lock", allowing dirty reads of the data. – datagod Aug 11 '12 at 17:51
  • 2
    The application is where you try to do things right, the database is where you can say "this is wrong, don't allow it". – jmoreno Aug 11 '12 at 18:58

You can store A and B columns in a separate table. Make sure your table has a primary key on A, and a unique constraint on (A,B) Refer to (A,B) from your table. The primary key on A will guarantee only one B per A. The foreign key without ON UPDATE CASCADE will make sure B does not change as long as the row is referred from the child table.

| improve this answer | |
  • 1
    Is the ROWVERSION really needed? Why not an FK from the original table to the new one: FOREIGN KEY (A, B) REFERENCES SeparateTable (A, B) – ypercubeᵀᴹ Aug 10 '12 at 18:11
  • @ypercube I think you are right and modified the answer accordingly. Thanks! – A-K Aug 10 '12 at 18:29
  • So, I'd have a table with a composite foreign key to a bridge table? Seems a bit strange, but that might be the best way to go... – jmoreno Aug 10 '12 at 19:23

UDFs that access other tables should generally be avoided in CHECK constraints (*).

There is almost no use case where they will behave entirely as expected by the user, there is almost certainly not a single SQL engine that implements them correctly according to the semantics as prescribed by the standard for CHECK constraints, brief, if you do this you're almost certainly headed to being the next in line to post yet another "CHECK constraint not working" question on some forum somewhere.

You said that "triggers seem more heavy duty". You're right. The problem is that you expect your engine to be able to infer all that "heavy duty" from your "simple" constraint declaration. That expectation is unwarranted.

(*) As always, the exception is of course the people who "know what they're doing". But if you were among those, you wouldn't have needed to ask anything in the first place.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.