Re: not null constraints, again

Hello,

On Thu, Apr 17, 2025, at 7:01 PM, Tender Wang wrote:
> create table t1(a int not null);
> ALTER TABLE t1 ADD CONSTRAINT d PRIMARY KEY(a), ALTER a DROP NOT NULL;
>
> in v17.4, ALTER TABLE successes, but in v18, it reports below error:
> ERROR: primary key column "a" is not marked NOT NULL

Yeah, I suppose this behavior is more or less expected. ALTER TABLE subcommands are reordered for execution on several passes (per AlterTablePass, which you're already familiar with). DROP commands are always executed first, so I suppose that what happens is that we first drop the not-null (and not queue addition of one because it already exists), then when time comes to add the PK we find (index_check_primary_key) that the column isn't not null.

I guess if you don't want to get this error, just don't run this command. It's quite useless anyway.

Note that if the column isn't not-null to start with, then this doesn't fail, yet you still end up with the column marked not-null.

--
Álvaro Herrera