sql: ADD CONSTRAINT...NOT VALID can be specified for unique constraints even though it is ignored.

[vy-ton]

Actual behavior

In CRDB,

[email protected]:26257/defaultdb> select * from vy;
  i
-----
  1
  1
(2 rows)
Time: 1ms total (execution 1ms / network 0ms)
[email protected]:26257/defaultdb> alter table vy add constraint c unique (i) not valid;
ERROR: duplicate key value violates unique constraint "c"
SQLSTATE: 23505
DETAIL: Key (i)=(1) already exists.
CONSTRAINT: c

Specifying NOT VALID should not enforce the constraint for existing rows until VALIDATE CONSTRAINT is called. Here, the NOT VALID appears to be ignored

Expected behavior
PG only allows specifying NOT VALID for check and foreign key constraints. CRDB should either error when NOT VALID is used with a unique constraint or defer enforcing the constraint for existing rows.

postgres=# alter table vy add constraint c unique (i) not valid;
ERROR:  UNIQUE constraints cannot be marked NOT VALID

Jira issue: CRDB-3249

[spilchen]

This one looks like is has been addressed already in PR #97746 (#96729). I manually verified the behaviour matches postgres now:

root@localhost:26257/defaultdb> create table vy (c1 int);
CREATE TABLE

Time: 6ms total (execution 6ms / network 0ms)

root@localhost:26257/defaultdb> insert into vy values (1), (1);
INSERT 0 2

Time: 8ms total (execution 8ms / network 0ms)

root@localhost:26257/defaultdb> alter table vy add constraint c unique (i) not valid;
ERROR: UNIQUE constraints cannot be marked NOT VALID
SQLSTATE: 0A000

[rafiss]

Nice find! In that case I'll close this out.