2

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.

Improve this question
0

2 Answers 2

Reset to default
5

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
0
3

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
3
  • Can you elaborate on your remark that "UDFs that access other tables should generally be avoided in CHECK constraints" - as surely that would be the other way around? ...a UDF that does a SELECT from the same table as the causative INSERT or UPDATE is going to behave strangely compared to doing a SELECT from an entirely unrelated table, no?
    – Dai
    Jun 13, 2022 at 18:30
  • It is related to the semantics of a CHECK constraint, which are that the CHECK expression must be satisfied by all rows in the table on which it is declared. Updates to other tables mentioned in a CHECK constraint could potentially invalidate this condition, meaning that such updates should be rejected in order to maintain full integrity. But that typically isn't implemented.
    – Erwin Smout
    Jun 21, 2022 at 11:41
  • The same applies to the case where the (access to the) "other table" is hidden away in some UDF. Updates to the other table can change the outcome of the UDF, which could invalidate the CHECK constraint itself, meaning such updates should be rejected once again. But this is even harder to detect because of what happens in the UDF itself.
    – Erwin Smout
    Jun 21, 2022 at 11:45

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.