references again

Hmm .. it didn't appear in the list, so I'll repost it ..

Another Question to ask the group, relating to 'REFERENCES', but I'll do
it here, so that my e-mail address doesn't get used.

I now have the 'clients' table ...

<http://www.chez.com/desmondcoughlan/sql/table.jpg>

.. and it seems to be the way I want it. Then the 'stock' table ..

<http://www.chez.com/desmondcoughlan/sql/table2.jpg>

Again, that seems all right to me. I then want to create a 'sales'
table, as in ('ventes' is French for 'sales') ..

CREATE TABLE ventes (
ventes_id SERIAL NOT NULL PRIMARY KEY CHECK (ventes_id > 0),
ventes_stock_id SERIAL NOT NULL REFERENCES stock (stock_id),
ventes_date_vendu date NOT NULL REFERENCES stock (stock_date_achete)\
CHECK (ventes.ventes_date_vendu >= stock.stock_date_achete),
ventes_prix_vendu numeric NOT NULL REFERENCES stock (stock_prix_achat)\
CHECK (ventes.ventes_prix_vendu > stock.stock_prix_achete)
);

It tells me ...

NOTICE: CREATE TABLE will create implicit sequence
"ventes_ventes_id_seq" for serial column "ventes.ventes_id"
NOTICE: CREATE TABLE will create implicit sequence
"ventes_ventes_stock_id_seq" for serial column "ventes.ventes_stock_id"
NOTICE: adding missing FROM-clause entry for table "stock"
ERROR: only table "ventes" can be referenced in check constraint

No comprendo !

Those last two statements are to create a 'when sold' date, and to
check that it doesn't take place
before the 'when bought' column in stock .. as obviously you can't sell
something that you haven't bought. :-\ The second is to check that the
sale price is higher than the price paid at purchase (selling at a loss
is illegal here in France).