remove PK from a composite PK when both keys have foreign keys constraint

Jorgefilemon 0
Viewed 25

i have an orderdetails table which has a composite PK.

(order_id, product_id)

i just want to have a single PK in this case order_id Both PK have foreign keys constraints.

i thought that by removing the foreign key constraint of product_id would let me remove the PK on product_id but it doesnt let me do so. i get

Cannot drop index 'PRIMARY': needed in a foreign key constraint

this is a slim down version of the DDL

CREATE TABLE `orderdetails` (
`order_id` bigint NOT NULL,
`product_id` int NOT NULL,

PRIMARY KEY (`order_id`,`product_id`),
KEY `fk_orders_has_products_orders1` (`order_id`),
KEY `fk_detailorder_products1` (`product_id`),

CONSTRAINT `fk_detailorder_products1` FOREIGN KEY (`product_id`) REFERENCES 
`product` (`product_id`) ON UPDATE CASCADE,

CONSTRAINT `fk_orders_has_products_orders1` FOREIGN KEY (`order_id`) REFERENCES 
`order` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

i tried to edit a copy of the dump detailorders table without the PK and constraints and also i get a message while importing

Missing index for constraint 'fk_detailN_detailO1' in the 
referenced table 'orderdetails'

i really dont know where that constraint name comes from as it is not written down in the original DDL

composite-key foreign-keys mysql
1 Answers

Not sure of the alter commands that were used, but to get it all straight:

  • Need to drop the primary key, and then re-add the new desired primary key:
    alter table orderdetails drop primary key, add primary key (order_id)

    After this query will show the primary key changed:

    enter image description here

  • Then drop the products-related constraint:
    alter table orderdetails drop constraint fk_detailorder_products1

    After this query will show the constraint removed:
    enter image description here

  • Then drop the products-related key:
    Alter table orderdetails drop key fk_detailorder_products1

    After this query will show the key removed:
    enter image description here

  • Then you can drop the product_id column if no longer needed, but I left this as-is.

Here's the db fiddle including those queries (and output results) as an example.

Paul T.0
Related