Mysql – How to Create relationship with more than one foreign key

MySQLPHP

I am using mysql and I have trouble in creating relationships of these table:

CREATE TABLE `db_menu` (
  `***menu_id***` int(255) NOT NULL,
  `menu_name` varchar(255) NOT NULL,
  `menu_code` varchar(255) NOT NULL,
  `menu_price` varchar(255) NOT NULL,
  `menu_image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_reserve` (
  `***user_id***` int(255) NOT NULL,
  `***menu_id***` int(255) NOT NULL,
  `quantity` varchar(255) NOT NULL,
  `total` varchar(255) NOT NULL,
  `date` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_sale` (
  `***user_id***` int(255) NOT NULL,
  `***menu_id***` int(255) NOT NULL,
  `quantity` varchar(255) NOT NULL,
  `total` varchar(255) NOT NULL,
  `date` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_status` (
  `***menu_id***` int(11) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db_user` (
  `***user_id***` int(255) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_pass` varchar(255) NOT NULL,
  `user_type` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

my problem is to create relationship of these tables

db_menu(menu_id) = db_status(menu_id) db_user(user_id) and

db_menu(menu_id) = db_reserve(user_id)(menu_id) db_user(user_id) and

db_menu(menu_id) = db_sale(user_id)(menu_id)

thank you!!!

Best Answer

I guess you want do something like this:

CREATE TABLE product (category INT NOT NULL,
                      id INT NOT NULL, price DECIMAL,
                      PRIMARY KEY(category, id)
                     ) ENGINE=INNODB;

CREATE TABLE customer (id INT NOT NULL,
                       PRIMARY KEY (id)
                      ) ENGINE=INNODB;

CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                            product_category INT NOT NULL,
                            product_id INT NOT NULL,
                            customer_id INT NOT NULL,
                            PRIMARY KEY(no),
                            INDEX (product_category, product_id),
                            INDEX (customer_id),
                            FOREIGN KEY (product_category, product_id)
                                REFERENCES product(category, id)
                                ON UPDATE CASCADE ON DELETE RESTRICT,
                            FOREIGN KEY (customer_id)
                                REFERENCES customer(id)
                          ) ENGINE=INNODB;

See more here

Related Question