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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

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