Re: locking of referenced table during constraint construction

On 4 Sep 2002, Scott Shattuck wrote:

> Under what conditions would the following statement cause the USERS
> table to lock out selects?
>
>
> alter table my_coupons
>   add constraint FK_mc_user_id
>   FOREIGN KEY (mc_frn_user_id)
>   REFERENCES users(user_ID);

If I'm reading code correctly, an exclusive lock
on the pk table is grabbed which will block selects
as well. You're effectively altering both tables
(you need to add triggers to both tables) and
both get locked.