Re: not null constraints, again - Mailing list pgsql-hackers
From | jian he |
---|---|
Subject | Re: not null constraints, again |
Date | |
Msg-id | CACJufxEiGZxm1Nh-0vUKCRVWLLdOT_Vh=_Lrk56V3Kj6J76eaA@mail.gmail.com Whole thread Raw |
In response to | Re: not null constraints, again (jian he <jian.universality@gmail.com>) |
Responses |
Re: not null constraints, again
|
List | pgsql-hackers |
+-- a PK in parent must have a not-null in child that it can mark inherited +create table inh_parent (a int primary key); +create table inh_child (a int primary key); +alter table inh_child inherit inh_parent; -- nope +alter table inh_child alter a set not null; +alter table inh_child inherit inh_parent; -- now it works +ERROR: relation "inh_parent" would be inherited from more than once in src/test/regress/sql/inherit.sql, the comments at the end of the command, seem to conflict with the output? ------------------------------------------------------------------------------- ALTER TABLE ALTER COLUMN SET NOT NULL implicitly means ALTER TABLE ALTER COLUMN SET NOT NULL NO INHERIT. So in ATExecSetNotNull if (conForm->connoinherit && recurse) ereport(ERROR, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot change NO INHERIT status of NOT NULL constraint \"%s\" on relation \"%s\"", NameStr(conForm->conname), RelationGetRelationName(rel))); should be if (conForm->connoinherit) ereport(ERROR, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot change NO INHERIT status of NOT NULL constraint \"%s\" on relation \"%s\"", NameStr(conForm->conname), RelationGetRelationName(rel))); then we can avoid the weird case like below: drop table if exists pp1; create table pp1 (f1 int not null no inherit); ALTER TABLE pp1 ALTER f1 SET NOT NULL; ALTER TABLE ONLY pp1 ALTER f1 SET NOT NULL; ------------------------------------------------------------------------------- + else if (rel->rd_rel->relhassubclass && + find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL) + { + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("not-null constraint on column \"%s\" must be removed in child tables too", + colName), + errhint("Do not specify the ONLY keyword.")); + } this part in ATExecDropNotNull is not necessary? per alter_table.sql <<<<<<---------->>>>>> -- make sure we can drop a constraint on the parent but it remains on the child CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL)); CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent); ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check"; <<<<<<---------->>>>>> by the same way, we can drop a not-null constraint ONLY on the parent, but it remains on the child. if we not remove the above part then ALTER TABLE ONLY DROP CONSTRAINT will behave differently from ALTER TABLE ONLY ALTER COLUMN DROP NOT NULL. example: drop table pp1,cc1, cc2; create table pp1 (f1 int not null); create table cc1 (f2 text, f3 int) inherits (pp1); create table cc2(f4 float) inherits(pp1,cc1); alter table only pp1 drop constraint pp1_f1_not_null; --works. alter table only pp1 alter column f1 drop not null; --- error, should also work. -------------------------------------------------------------------------------
pgsql-hackers by date: