I have a category table which currently has only a title field (PK)
CREATE TABLE `category` (
`title` varchar(20),
PRIMARY KEY (`title`)
);
and a product table with a composite key as a primary key
CREATE TABLE `product` (
`title` varchar(50),
`vendor_name` varchar(50),
`price` decimal(10,2) unsigned NOT NULL,
PRIMARY KEY (`title`, `vendor_name`),
CONSTRAINT `fk_product_vendor_name` FOREIGN KEY (`vendor_name`) REFERENCES `vendor` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
);
A product can have many categories so I created a junction table. I'm struggling with the composite key from the product table. In the junction table I need to reference the combination of the product title and the vendor name foreign key. I started with this
CREATE TABLE `category_product` (
``/* product composite PK */
`category_title` varchar(20),
PRIMARY KEY (``/* product composite PK */,`category_title`),
KEY `fk_category_product_category_title` (`category_title`),
KEY ``/* product composite PK */
CONSTRAINT `fk_category_product_category_title` FOREIGN KEY (`category_title`) REFERENCES `category` (`title`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT ``/* product composite PK */ ON DELETE CASCADE ON UPDATE CASCADE
);
How do I handle the composite key from the product table?
Thanks in advance
Best Answer
You have also to define two columns for a double Primary KEY