Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012
    Posts
    1

    Unanswered: Problem with insert into M:N relation tables

    Hi, I have a little problem. I have these three tables:

    Code:
    -- -----------------------------------------------------
    -- Table `category`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `category` (
      `cat_id` INT NOT NULL AUTO_INCREMENT ,
      `cat_name` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`cat_id`) )
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8
    COLLATE = utf8_czech_ci;
    
    
    -- -----------------------------------------------------
    -- Table `products`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `products` (
      `prod_id` INT NOT NULL AUTO_INCREMENT ,
      `prod_name` VARCHAR(45) NOT NULL ,
      `prod_producer` VARCHAR(45) NOT NULL ,
      `prod_describe` VARCHAR(250) NULL ,
      `prod_price` INT NOT NULL ,
      PRIMARY KEY (`prod_id`) )
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8
    COLLATE = utf8_czech_ci;
    
    -- -----------------------------------------------------
    -- Table `category_has_products`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `category_has_products` (
      `cat_id` INT NOT NULL ,
      `prod_id` INT NOT NULL ,
      PRIMARY KEY (`cat_id`, `prod_id`) ,
      CONSTRAINT `fk_category_has_products_category`
        FOREIGN KEY (`cat_id` )
        REFERENCES `category` (`cat_id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_category_has_products_products`
        FOREIGN KEY (`prod_id` )
        REFERENCES `products` (`prod_id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION);
    For example I have in table Category two columns - Toys and Gifts. And I dont know, how can I insert for example Teddy Bear into both category and if I need then use eg. this:
    Code:
    select * from products where id_cat = 1;
    I am sorry for my english. Thanks for you help.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I think what you are asking for is once you have INSERTed into the Category table you would like to use the unique identifier value for other INSERTs in the category_has_products table?

    To get the last inserted id there is a function called LAST_INSERT_ID(). Here is an example of how this works:

    Code:
    mysql> create table categories(id tinyint unsigned auto_increment primary key, category varchar(20));
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into categories(category) values ('test');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select last_insert_id();
    +------------------+
    | last_insert_id() |
    +------------------+
    |                1 | 
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> insert into categories(category) values ('test2');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select last_insert_id();
    +------------------+
    | last_insert_id() |
    +------------------+
    |                2 | 
    +------------------+
    1 row in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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