inserting references

On Fri, Jun 14, 2002 at 01:14:08AM -0700, ktt wrote:

I have a table, which references
values in other 3 tables.

I would like to insert values in
bananas, oranges and kivi tables
and insert their bananas_id,oranges_id,kivi_id,
country
in fruits table.

What are better methods for doing
this(exept LAST_INSERT_ID())?
Would be nice to use the most compact form.

If you're using sequences, just use currval() in the insert statement for
fruits.

sample tables scheme

----------- ---------- ---------- ----------

fruits bananas oranges kivi

----------- ---------- ---------- ----------

fruits_id bananas_id oranges_id kivi_id

bananas_id date_bn date_or date_kv

oranges_id ---------- ---------- ----------

kivi_id
country

-----------

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

On Fri, Jun 14, 2002 at 01:14:08AM -0700, ktt wrote:

I have a table, which references
values in other 3 tables.

I would like to insert values in
bananas, oranges and kivi tables
and insert their bananas_id,oranges_id,kivi_id,
country
in fruits table.

If you're using sequences, just use currval() in the insert statement for
fruits.

If this is always supposed to happen, why have the client issue insert
commands against fruits at all? Set up ON INSERT triggers on the detail
tables that issue inserts into the summary table. They can get all the
info they need from the "new" record for the detail table.

regards, tom lane