public inbox for [email protected]  

help / color / mirror / Atom feed

From: Richard Huxton <[email protected]>
To: [email protected]
Subject: Column and table constraints - anally retentive comments
Date: Fri, 15 Nov 2002 15:18:26 +0000
Message-ID: <[email protected]> (raw)

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 
syntax. It is not attached to a particular column, instead it appears as a 
separate item in the comma-separated column list. Column definitions and 
these constraint definitions can be listed in mixed order.

We say that the first two constraints are column constraints, whereas the 
third one is a table constraint because it is written separately from the 
column definitions. Column constraints can also be written as table 
constraints, while the reverse is not necessarily possible. The above example 
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. 
---end extract---

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

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

-- 
  Richard Huxton

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Column and table constraints - anally retentive comments
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox