Search Postgresql Archives

Re: Constraint name for named NOT NULL constraints is ignored

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux