Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2008
    Posts
    53

    Unanswered: inserting 3. rows - relationships

    I am new in database. I have tables:

    Code:
    books:
    id_book
    title
    
    books_authors:
    id_book
    id_author
    
    authors:
    id_author
    name

    I have relationships:
    books - books_authors (1 to many); authors - book_authors (1 to many).

    I make querys for example:

    Code:
    INSERT INTO books(title) VALUES('title1');
    INSERT INTO authors(name) VALUES('Aaaa Bbbb');
    And now I should write query to insert id_book and id_author in table 'books_authors' but I don't know id_book and id_author from previous querys. What should I do ? I would like some advice which will work in Oracle also in MySQL. Could you help me ?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You can not insert something you don't know.

    As your current INSERT statements insert only "names", there are no IDs at all (unless they are populated by database triggers). Anyway, as Oracle can not know who wrote which book, it is you who should do that by specifying both ID values in the INSERT statement, for example:
    Code:
    INSERT INTO book_authors
      (id_book, id_author)
        VALUES
      (13, 224);

  3. #3
    Join Date
    Dec 2008
    Posts
    53
    It is strange. We have for example Internet shop and customer order something on the web site, so his products are add to some table in database which must have relation which other table but we must know id added row from first table to add row to second table, so how can we solve that ?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    When customer comes to the web site, he/she has to identify him/herself - that's how you know his/her ID.

    When he buys something (i.e. puts a product into a basket), you know what he/she has selected - that's how you know product ID.

    Now, it isn't that difficult to insert both IDs, is it?

    In your case, you know only author's name and book's name. There may be many authors and many books, but you have to join them manually. Or, if it was an application, someone has to select the author and his/her book (using lists of values, for example).

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think chomik is asking about serial numbers

    serial numbers are a feature of all database systems, but not always implemented the same way

    1. the id number can be automatically assigned when the book or the author is inserted, and a special function (e.g. LAST_INSERT_ID in MySQL) retrieves the value of the id number assigned, to be used in the subsequent insert into the books_authors table

    2. you draw the next serial number from a stack (e.g. NEXTVAL for Oracle sequences), and then use it for all inserts

    these methods are fundamentally different, you will not be able to use the same method in both MySQL and Oracle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, perhaps this is what he is talking about - partially!

    Because,
    I should write query to insert id_book and id_author in table 'books_authors' but I don't know id_book and id_author from previous querys.
    suggests different.

  7. #7
    Join Date
    Dec 2008
    Posts
    53
    I have a question about NEXTVAL.

    Is it possible to have two the same NEXTVAL ? For example: User 1 takes NEXTVAL, User 2 takes NEXTVAL, User 1 makes insert using NEXTVAL, User 2 makes insert using NEXTVAL and he will have an error ?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, it is not

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    NEXTVAL is what its name says - the next value. Every NEXTVAL is different from another (previous or next) NEXTVAL. So, just like Rudy has said - there's no reason to worry that its value may be duplicated.

    Though - there is the CURRVAL (current sequence value) which is the same as the "previous next value":
    Code:
    SQL> create sequence myseq;
    
    Sequence created.
    
    SQL> select myseq.nextval from dual;
    
       NEXTVAL
    ----------
             1
    
    SQL> select myseq.nextval from dual;
    
       NEXTVAL
    ----------
             2
    
    SQL> select myseq.currval from dual;
    
       CURRVAL
    ----------
             2
    
    SQL> select myseq.currval from dual;
    
       CURRVAL
    ----------
             2
    
    SQL>

  10. #10
    Join Date
    Dec 2008
    Posts
    53
    thx a lot

Posting Permissions

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