Поиск
Список
Период
Сортировка
Искать

От:
"Steve SAUTETNER" <ssa@informactis.com>
Дата:
Hello !

I've got 2 databases. In the first i've got users described in a table
(name, adress ..).
In the second, i've got a table with products informations (id, price, owner
...).
I must create a trigger in the second table on insert that verify that the
product's owner exists in the first database.

Anyone has an idea on how to do it ?
(i can't merge the two databases)

have a nice day !

Re:

От:
Jason Earl <jdearl@yahoo.com>
Дата:
This is basically what foreign keys are designed to
do.

If you haven't used them before Bruce Momjian's online
book covers the details, and it is PostgreSQL
specific.

http://www.ca.postgresql.org/docs/awbook.html

As an example let's say that you had the following
simple tables:

CREATE TABLE customers (
id         SERIAL PRIMARY KEY,
name       varchar(50)    
);

CREATE TABLE products (
id         SERIAL PRIMARY KEY,
name       varchar(50),
owner_id   int
);

adding a simple constraint will do what you want:

ALTER TABLE products ADD CONSTRAINT owner_id_fk
FOREIGN KEY (owner_id) REFERENCES customers(id);

I tend to write that a little differently, in that I
include it in the table definition like so:

CREATE TABLE products (
id         SERIAL PRIMARY KEY,
name       varchar(50),
owner_id   int REFERENCES customers(id)
); 

There's a lot more that you can do with this nifty
tool, but Bruce tells the story much better than I
would.

Hope this is helpful,
Jason
--- Steve SAUTETNER  wrote:
> Hello !
> 
> I've got 2 databases. In the first i've got users
> described in a table
> (name, adress ..).
> In the second, i've got a table with products
> informations (id, price, owner
> ...).
> I must create a trigger in the second table on
> insert that verify that the
> product's owner exists in the first database.
> 
> Anyone has an idea on how to do it ?
> (i can't merge the two databases)
> 
> have a nice day !
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly


__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
FAQ