diff options
| -rw-r--r-- | src/backend/commands/tablecmds.c | 25 | ||||
| -rw-r--r-- | src/test/regress/expected/constraints.out | 4 | ||||
| -rw-r--r-- | src/test/regress/sql/constraints.sql | 3 |
3 files changed, 32 insertions, 0 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 5fd8b51312c..34f9e342362 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -8281,6 +8281,31 @@ ATExecAddIdentity(Relation rel, const char *colName, errmsg("column \"%s\" of relation \"%s\" must be declared NOT NULL before identity can be added", colName, RelationGetRelationName(rel)))); + /* + * On the other hand, if a not-null constraint exists, then verify that + * it's compatible. + */ + if (attTup->attnotnull) + { + HeapTuple contup; + Form_pg_constraint conForm; + + contup = findNotNullConstraintAttnum(RelationGetRelid(rel), + attnum); + if (!HeapTupleIsValid(contup)) + elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"", + colName, RelationGetRelationName(rel)); + + conForm = (Form_pg_constraint) GETSTRUCT(contup); + if (!conForm->convalidated) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("incompatible NOT VALID constraint \"%s\" on relation \"%s\"", + NameStr(conForm->conname), RelationGetRelationName(rel)), + errhint("You might need to validate it using %s.", + "ALTER TABLE ... VALIDATE CONSTRAINT")); + } + if (attTup->attidentity) ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index dda67798cb3..1bbf59cca02 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -1404,6 +1404,10 @@ ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a); ERROR: cannot create primary key on column "a" DETAIL: The constraint "nn" on column "a" of table "notnull_tbl1", marked NOT VALID, is incompatible with a primary key. HINT: You might need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT. +-- cannot set column as generated-as-identity if it has an invalid not-null +ALTER TABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ERROR: incompatible NOT VALID constraint "nn" on relation "notnull_tbl1" +HINT: You might need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT. -- ALTER column SET NOT NULL validates an invalid constraint (but this fails -- because of rows with null values) ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 0a6290bc571..733a1dbccfe 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -832,6 +832,9 @@ ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a; -- cannot add primary key on a column with an invalid not-null ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a); +-- cannot set column as generated-as-identity if it has an invalid not-null +ALTER TABLE notnull_tbl1 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; + -- ALTER column SET NOT NULL validates an invalid constraint (but this fails -- because of rows with null values) ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; |