Alter Function with Check Constraint Dependencies

Question

Alter Function with Check Constraint Dependencies

AVB

SSChampion

Points: 11418
More actions
December 4, 2007 at 8:22 am

#98686

I wrote a function that is used by several Check Constraints. Now I want to alter the function an it errors when I atttempt to:
Cannot ALTER 'dbo.function'because it is being referenced by object 'CK_Function'.
I am assuming that I would have to remove the check constraints that are referencing the function first. Then alter the function and then rebuild the constraints.
If this is the only way to do it then how do you ensure that data isn't inserted into the tables while check constraints have been removed?
I don't want data to be inserted/modified without the appropiate constraints in place.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

There isn't another way, and the way to do it is script out the removes, the alter of the function, and the adding back of the constraints and put it all in a transaction.

You don't want to be editing this by hand. Edit on a test system, script the drops and recreates, put your function in the middle, and then run it as a transaction. You could do quick testing before the commit, but in any case, I'd have scripts ready to put the function back the way is was ASAP if something is wrong.

AVB SSChampion Points: 11418 More actions · Answer 2

Thanks Steve for the replies to both posts. I always looking forward to reading what you have to say.

So I'd have to write:

Drop the constraint objects

Then an Alter (or Drop and Create) for the Function

Then Create and Add Constraints again.

I've never done this.. guess it'll great practice...

While this happens could I lock the table so no data could be inserted?

AVB SSChampion Points: 11418 More actions · Answer 3

I wrote the script tooled around with it. In the script I set the Transaction Isolation Level to Serializable so no data could be modified without the constraints in effect. I believe this should be the correct way to do it.