Re: Postgres unique index checking and atomic transactions

On 24 Jul 2003, Greg Stark wrote:

> Dmitry Tkach <dmitry@openratings.com> writes:
>
> > The good news though is that, if you drop (or disable) your pk index
>
> That's what I did, except I had to cascade to the foreign keys and then
> recreate them too. And you can't really recreate a primary key constraint, you
> just get a unique index which I think is equivalent.
>
> And that's another wishlist item. It would be nice to be able to disable
> constraints without dropping them and without poking around in catalog tables
> manually.
>
> Ie, it would be nice to be able to do
>
>  alter table foo disable constraint "$1"
>
> and then later do
>
>  alter table foo enable constraint "$1"
>
> and have postgres optionally recheck the constraint or not. It would be a lot
> safer than potentially accidentally recreating the constraint incorrectly. And
> a lot safer than poking around in the catalog tables.

We probably should do this, but there are some issues.  If you violate a
constraint while it's disabled and then do not check the constraint again
the system may not do what you want in the future for that or related
constraints (like if you violate a primary key that is referenced by a
foreign key).  Also, in a strict sense, if you disable a constraint and
later enable it again without checking, you no longer have that
same constraint.

Also, for foreign keys, this might involve rechecking the fk table at
enable time.  Right now our implementation of that type of check is pretty
poor (I really need to fix that eventually... :( ).