Content-Type: text/plain;
  charset="us-ascii"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: pgsql-docs@postgresql.org
Subject: Column and table constraints - anally retentive comments
Date: Fri, 15 Nov 2002 15:18:26 +0000
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200211151518.26707.dev@archonet.com>

Probably just me, but in
http://developer.postgresql.org/docs/postgres/ddl-constraints.html#AEN1793

---begin extract---
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

The first two constraints should look familiar. The third one uses a new=20
syntax. It is not attached to a particular column, instead it appears as a=
=20
separate item in the comma-separated column list. Column definitions and=20
these constraint definitions can be listed in mixed order.

We say that the first two constraints are column constraints, whereas the=
=20
third one is a table constraint because it is written separately from the=
=20
column definitions. Column constraints can also be written as table=20
constraints, while the reverse is not necessarily possible. The above examp=
le=20
could also be written as

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

or even

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

It's a matter of taste.=20
---end extract---

Not quite taste I'd have thought. Column (value) related constraints should=
 be=20
written as such, not disconnected from their column. Same principle as=20
declaring variables near to where they are needed.

In the example given it would make more sense to have a domain "product_pri=
ce"=20
with a constraint on it. Now, I realise we can't do the constraint in 7.3 b=
ut=20
later on people will find it easier to maintain if we encourage good=20
behaviour.

--=20
  Richard Huxton