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
