Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002

    inserting into 2 tables simultaneously

    i have 2 test tables set up:

    CREATE TABLE products
    product_id SERIAL PRIMARY KEY,
    note VARCHAR(100)

    CREATE TABLE videos
    video_id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE

    videos is a sub-type of products

    I am thinking about creating a view so i can insert into the 2 tables at once, but it seems SERIAL will cause a problem coz I would not be able to assign a number to that column.

    so do i have to use 2 separate queries to update the 2 table? or is there a clever way to do it thru views?

  2. #2
    Join Date
    Sep 2002

    Re: inserting into 2 tables simultaneously

    I am not familiar with this "SERIAL" datatype as I work with Oracle which does not have it - but I can guess what it does. You would need know the value of the new product_id used in the products insert when you did the videos insert. I can only think of 2 ways of doing that, and I have no idea whether your DBMS supports either:

    1) Is there a RETURNING clause for insert statements? If so:

    INSERT INTO products (note) VALUES ('aaa') RETURNING product_id into v; /* where v is a variable in your trigger */
    INSERT INTO videos (product_id) VALUES (v);

    2) Can you override the SERIAL value (sounds like the answer is NO!) If you could:

    v := NEXT VALUE FOR product_sequence;
    INSERT INTO products( product_id, note ) VALUES (v, 'aaa');
    INSERT INTO videos( product_id ) VALUES (v);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts