[NOVICE] references again

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[NOVICE] references again

Desmond Coughlan
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).



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] references again

Michael Glaesemann

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org