mysql composite foreign key referencing more than 2 attributes

Question

Hi I have a table with 3 attributes as primary key Products: PRIMARY KEY (product_name,category,product_type).

Now i am referencing this composite primary key in another table

order_details: foreign key(product_name,product_type,category) references products(product_name,product_type,category)

however I am getting an error in the console saying missing parenthesis and I am not able to add the foreign key. But if i add only 2 column names in the reference(example : "foreign key(product_name,product_type,category) references products(product_name,product_type) ") the query is not giving an error.

Please help me to resolve this issue. Please find my code below

CREATE TABLE `products` (
  `product_name` varchar(45) NOT NULL,
  `product_type` varchar(45) NOT NULL,
  `category` varchar(45) NOT NULL,
  `product_desc` varchar(150) DEFAULT NULL,  
  `unit_price` int(11) NOT NULL,
  `supplier_id` int(11) NOT NULL,
  `units_in_stock` int(11) NOT NULL,
  PRIMARY KEY (`product_name`,`category`,`product_type`),
  INDEX (product_name,category,product_type),
  CONSTRAINT `supplier_prod_table_fkey` FOREIGN KEY (`supplier_id`) REFERENCES 
  `supplier` (`supplier_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB;

CREATE TABLE `order_details` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(45) NOT NULL,
  `product_type` varchar(45) NOT NULL,
  `category` varchar(45) NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  CONSTRAINT `orderid_fkey` FOREIGN KEY (`order_id`) REFERENCES `orders`
  (`order_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  PRIMARY KEY (`order_id`,`product_name`,`product_type`,`category`),
  INDEX (product_name,product_type,category),
  foreign key(product_name,product_type,category) references products(product_name,product_type,category)

);

saaj saaj · Accepted Answer · 2015-03-31T10:21:07

Fields in REFERENCES and in correspondent indices must go in the same order in both tables, i.e. (product_name, category, product_type).

CREATE TABLE `order_details` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(45) NOT NULL,
  `product_type` varchar(45) NOT NULL,
  `category` varchar(45) NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  CONSTRAINT `orderid_fkey` FOREIGN KEY (`order_id`) REFERENCES `orders`
  (`order_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  PRIMARY KEY (`order_id`,`product_name`,`product_type`,`category`),
  INDEX (product_name,category,product_type),
  FOREIGN KEY(product_name,category,product_type) REFERENCES products(product_name,category,product_type)

);

mysql composite foreign key referencing more than 2 attributes

1 Answers