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.

0

2 Answers 2

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.

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.

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.