Re: Accidentally dropped constraints: bug?

> On Aug 5, 2021, at 12:35 AM, Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com> wrote:
>
> Noting that all constraints have been removed, not just the ones
> wholly dependent on dropped columns.

I don't find this all that surprising. If CHECK (a > 5 AND b IS NOT NULL AND c > 10) is really meant as three independent checks, it should be written that way. However, for any row where c is null, the entire expression will either be null or false, and the check will fail precisely when (a > 5 AND b IS NOT NULL) is false. So if you imagine the dropped column as a column of phantom null values, you'd expect the check to still reject rows where a <= 5 or b is null.

Is it reasonable to imagine the dropped column as implicitly full of nulls? That's how an added column works, but do we think about dropped columns that way?

In any event, the documentation is pretty clear about this:

> DROP COLUMN [ IF EXISTS ]
> This form drops a column from a table. Indexes and table constraints involving the column will be automatically dropped as well.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company