Обсуждение: Constraint name for named NOT NULL constraints is ignored

While upgrading our schema between application versions, we also had a
few constraint changes. Some of those changes were dropping NOT NULL
constraints on some columns. Our schema had a few such NOT NULL
constraints, which were created using the named variant of the column
constraint clause (something like '... col_name col_type CONSTRAINT
nn_col_name NOT NULL ...'). This syntax is happily accepted by postgres.
So our schema change script was expecting that simply dropping those
named constraints will drop the NOT NULL constraint on the relevant
columns, but the constraint is just simply not there at all, so trying
to drop it gives an error.

From the description of the pg_constraint table
http://www.postgresql.org/docs/8.2/static/catalog-pg-constraint.html
is clear that it does not hold NOT NULL constraints, which go to the
pg_attribute table, but then maybe an error should be raised if somebody
tries to create a named NOT NULL constraint ? Ignoring integral parts of
the SQL syntax feels somewhat mySQL-ish. Or at least mention this
behavior on the CREATE TABLE page (I couldn't find it if it's there):
http://www.postgresql.org/docs/8.2/static/sql-createtable.html

Cheers,
Csaba.

Csaba Nagy wrote:
> While upgrading our schema between application versions, we also had a
> few constraint changes. Some of those changes were dropping NOT NULL
> constraints on some columns. Our schema had a few such NOT NULL
> constraints, which were created using the named variant of the column
> constraint clause (something like '... col_name col_type CONSTRAINT
> nn_col_name NOT NULL ...'). This syntax is happily accepted by postgres.
> So our schema change script was expecting that simply dropping those
> named constraints will drop the NOT NULL constraint on the relevant
> columns, but the constraint is just simply not there at all, so trying
> to drop it gives an error.
>
> From the description of the pg_constraint table
> http://www.postgresql.org/docs/8.2/static/catalog-pg-constraint.html
> is clear that it does not hold NOT NULL constraints, which go to the
> pg_attribute table, but then maybe an error should be raised if somebody
> tries to create a named NOT NULL constraint ? Ignoring integral parts of
> the SQL syntax feels somewhat mySQL-ish. Or at least mention this
> behavior on the CREATE TABLE page (I couldn't find it if it's there):
> http://www.postgresql.org/docs/8.2/static/sql-createtable.html

I don't think we can throw an error for such cases because it would
prevent us from accepting valid SQL statements.  I even see an example
in our CREATE TABLE manual page:

    CREATE TABLE distributors (
        did     integer CONSTRAINT no_null NOT NULL,
        name    varchar(40) NOT NULL
    );

Added to TODO:

        o Have CONSTRAINT cname NOT NULL record the contraint name

          Right now pg_attribute.attnotnull records the NOT NULL status
          of the column, but does not record the contraint name


--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +