Re: Constraint name for named NOT NULL constraints is ignored

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. +