Unanswered: Adding a column to parent table with LOTS of FK constraints
I have a parent table A, that has 40-50 child tables defined through R.I. constraints. During normal application processing, we have tried to add a new column to the parent table but get contention locking issues, I'm assuming with all the child keys that are constantly being checked back to the parent. We are a 24x7 shop with no downtime where we can add the column outside of normal processing. I'm guessing we can't put the column on the parent table unless we were to find a slice of time where nothing was happening against any of the children, although I don't know this is true or not.
I have thought about solutions creating a 1:1 table off to the side to tie back to the parent that would contain the key from the parent and any new columns we want to add, but was wondering if there are any other 'tricky' ways of adding this column to the parent directly without taking the application down.
Is the column that you are adding also a foreign-key column , if so does it have an enforced constraint?
Sometimes it's necessary to lock tables in exclusive mode for taking some actions, but the application has to be designed (and db configured) appropriately to handle any resulting lock-timeouts elegantly. You won't have downtime, but you will have temporary waits and delays to impacted functional areas.
Remember also that FK constraints can be temporarily marked as not enforced (but there's a cost later to enforce them again).