Обсуждение: 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).

On May 22, 2005, at 4:53 PM, D.C. wrote:

> 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

As the error states, CHECK constraints currently only permit
comparisons within the same table. To compare values across tables,
such as you are looking to do, you need to use a trigger that will
raise an error if the conditions you desire are not met.

Check out the CREATE TRIGGER documentation in the SQL command
reference in the PostgreSQL docs. You're probably interested in
BEFORE INSERT and BEFORE UPDATE triggers.

<http://www.postgresql.org/docs/8.0/interactive/sql-createtrigger.html>

Hope this helps.

Michael Glaesemann
grzm myrealbox com

PS. Regarding your earlier question regarding your email address
showing up in your newreader, I don't think there's a whole lot that
can be done about that, though I wouldn't mind being proven wrong.
You can take some comfort in knowing that email addresses are
obscured in the www.postgresql.org mailing list archives.