Re: not null constraints, again - Mailing list pgsql-hackers

copy from src/test/regress/sql/index_including.sql
-- Unique index and unique constraint
CREATE TABLE tbl_include_unique1 (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl_include_unique1 SELECT x, 2*x, 3*x, box('4,4,4,4')
FROM generate_series(1,10) AS x;
CREATE UNIQUE INDEX tbl_include_unique1_idx_unique ON
tbl_include_unique1 using btree (c1, c2) INCLUDE (c3, c4);
ALTER TABLE tbl_include_unique1 add UNIQUE USING INDEX
tbl_include_unique1_idx_unique;
\d+ tbl_include_unique1

transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
                /* Ensure these columns get a NOT NULL constraint */
                cxt->nnconstraints =
                    lappend(cxt->nnconstraints,
                            makeNotNullConstraint(makeString(attname)));
the above code can only apply when (constraint->contype ==
CONSTR_UNIQUE ) is false.
The above sql example shows that (constraint->contype == CONSTR_UNIQUE
) can be true.



drop table if exists idxpart, idxpart0 cascade;
create table idxpart (a int) partition by range (a);
create table idxpart0 (a int not null);
alter table idxpart attach partition idxpart0 for values from (0) to (100);
alter table idxpart alter column a set not null;
alter table idxpart alter column a drop not null;

"alter table idxpart alter column a set not null;"
will make idxpart0_a_not_null constraint islocal and inhertited,
which is not OK?
for partition trees, only the top level/root can be local for not-null
constraint?

"alter table idxpart alter column a drop not null;"
should cascade to idxpart0?



   <para>
    However, a column can have at most one explicit not-null constraint.
   </para>
maybe we can add a sentence:
"Adding not-null constraints on a column marked as not-null is a no-op."
then we can easily explain case like:
create table t(a int primary key , b int, constraint nn  not null a );
the final not-null constraint name is "t_a_not_null1"



    /*
     * Run through the constraints that need to generate an index, and do so.
     *
     * For PRIMARY KEY, in addition we set each column's attnotnull flag true.
     * We do not create a separate not-null constraint, as that would be
     * redundant: the PRIMARY KEY constraint itself fulfills that role.  Other
     * constraint types don't need any not-null markings.
     */
the above comments in transformIndexConstraints is wrong
and not necessary?
"create table t(a int primary key)"
we create a primary key and also do create separate a not-null
constraint for "t"



                /*
                 * column is defined in the new table.  For PRIMARY KEY, we
                 * can apply the not-null constraint cheaply here.  Note that
                 * this isn't effective in ALTER TABLE, unless the column is
                 * being added in the same command.
                 */
in transformIndexConstraint, i am not sure the meaning of the third
sentence in above comments



i see no error message like
ERROR:  NOT NULL constraints cannot be marked NOT VALID
ERROR:  not-null constraints for domains cannot be marked NO INHERIT
in regress tests. we can add some in src/test/regress/sql/domain.sql
like:

create domain d1 as text not null no inherit;
create domain d1 as text constraint nn not null no inherit;
create domain d1 as text constraint nn not null;
ALTER DOMAIN d1 ADD constraint nn not null NOT VALID;
drop domain d1;