Foreign keys with mySQL
I have a table that references a reservation and a product, but I can't add any foreign key.
Here is the table :
CREATE TABLE IF NOT EXISTS `resa_product` (
`id_reservation` int(10) NOT NULL,
`id_business` int(10) NOT NULL,
`id_category` int(10) NOT NULL,
`id_product` int(10) NOT NULL,
`quantity` int(11) NOT NULL,
PRIMARY KEY (`id_reservation`,`id_business`,`id_category`,`id_product`),
KEY `resa_prod_index` (`id_business`,`id_category`,`id_product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
The product table :
CREATE TABLE IF NOT EXISTS `product` (
`id_business` int(10) NOT NULL,
`id_product` int(10) NOT NULL AUTO_INCREMENT,
`id_category` int(10开发者_如何转开发) NOT NULL,
`nom` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
...
PRIMARY KEY (`id_product`,`id_category`,`id_business`),
KEY `id_category` (`id_category`),
KEY `id_business` (`id_business`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
but when I try this, I get the errno 150 from mySQL :
ALTER TABLE `resa_product`
ADD FOREIGN KEY (`id_business`, `id_category`, `id_product`)
REFERENCES `product`(`id_business, `id_category`, `id_product`)
ON UPDATE CASCADE
ON DELETE RESTRICT;
I don't understand why I can't insert this composed key, although I added an index. Do someone has an idea ?
thanks for your help
In the product table
- data type and collation of the 3 columns must match
- there must be a unique constraint or index on the 3 columns in the same order as the FK
Edit: after question update.
Change the foreign key to this to align column order to the PK of product
ALTER TABLE `resa_product`
ADD FOREIGN KEY (`id_product`, `id_category`, `id_business`)
REFERENCES `product`(`id_product, `id_category`, `id_business`)
ON UPDATE CASCADE
ON DELETE RESTRICT;
However, the 3 columns appear in different orders all over the show. I'd fix this to be consistent personally...
Most common reason for this problem is a slight difference between the columns in products
and resa_product
. The field types need to be exactly the same, so the size/precision and the sign (unsigned or not) all need to match.
精彩评论