|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
3.3.2 Not-Null Constraints
A not-null constraint simply specifies that a column must not assume the null value. A syntax example:
CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric );
A not-null constraint is always written as a column constraint. A
not-null constraint is functionally equivalent to creating a check
IS NOT NULL), but in
PostgreSQL creating an explicit
not-null constraint is more efficient. The drawback is that you
cannot give explicit names to not-null constraints created this
Of course, a column can have more than one constraint. Just write the constraints one after another:
CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0) );
The order doesn't matter. It does not necessarily determine in which order the constraints are checked.
NOT NULL constraint has an inverse: the
NULL constraint. This does not mean that the
column must be null, which would surely be useless. Instead, this
simply selects the default behavior that the column might be null.
NULL constraint is not present in the SQL
standard and should not be used in portable applications. (It was
only added to PostgreSQL to be
compatible with some other database systems.) Some users, however,
like it because it makes it easy to toggle the constraint in a
script file. For example, you could start with:
CREATE TABLE products ( product_no integer NULL, name text NULL, price numeric NULL );
and then insert the
NOT key word where desired.
Tip: In most database designs the majority of columns should be marked not null.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|