Re: Adding "NOT NULL" Constraint with CREATE TABLE or ALTER TABLE

On Thu, Dec 05, 2002 at 09:40:55AM -0500, Phillip J. Allen wrote:
> Hi all,
>
> I have built my geology/mine database in Postgres and the front end in
> MS Access(I hate it too but still haven't decided on a good
> alternative).  Now when I ported my dbf to Postgres I didn't put all the
> constraints and referencial integrety (relationships) into it so I am
> now going back through it and refining it.
>
> Now I am adding the "not null" constraints to various columns.  I am
> adding them with the ALTER TABLE mytable ADD CONSTRAINT thekeyname_key
> CHECK (thefield IS NOT NULL);.  When I do a \d+ thetable, psql returns
> the proper constraint and all works fine.  But the constraint (not null)
> does not appear in the modifiers colunm of the \d+ command but below the
> column list as a Constraint.
> Is adding the not null constraint at the time of creating a table the
> same as adding a "constraint" later? If it is different is there a
> performance difference?

Phillip --

The CONSTRAINT CHECK() will work, but it's not the same as a "natural"
NOT NULL. You're adding a CHECK which could check _anything_, as far as
PG is concerned -- CHECK(var > 7), CHECK(var IS NOT NULL), etc. are all
legal. PG doesn't parse this to figure out 'aha! it's a not null check'
to mark the column as such in the system tables.

No problem with how you're doing it, but it won't work exactly the same
-- Access, for example, will give a different error message if you
failed the CHECK(... is not null) constraint than if you fail a "normal"
NOT NULL constraint.


What you want is

ALTER TABLE [ ONLY ] table [ * ]
  ALTER [ COLUMN ] column { SET | DROP } NOT NULL

so you can say

ALTER TABLE t ALTER col SET NOT NULL;

which will mark it as not-null in the "right" way so it appears in tbl
listings as such.

This feature is, unfortunately, only available in 7.3. A very worthwhile
upgrade, though, so if you can, do it.


If you can't upgrade, you can still do this by messing with the system
tables. A lot of pre-7.3 schema change workarounds are documented in an
article at http://techdocs.postgresql.org/techdocs/updatingcolumns.php.

To change the email field to NOT NULL :

UPDATE pg_attribute SET attnotnull  = TRUE
WHERE attname = 'email'
AND attrelid = ( SELECT oid FROM pg_class WHERE relname = 'pers') ;


--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant