How to drop doctrine foreign key in MySQL Database

Sometimes is necessary to drop manually database tables created by Doctrine.
Problem begins when we have relations marked as foreign keys in database tables.
MySQL don’t let us drop the table until relations exist. Few step how to delete foreign key relations.

MySQL Queries

We need two query. First to find constraints names. Second to drop them.

1) Find MySQL name of constraint.
We can use query:

SHOW CREATE TABLE {table_name}

In result we have to find constraints names. There start from:
FK_%
2) Drop the foreign key constraint from MySQL Table
We can use query:

ALTER TABLE {Table_Name} DROP FOREIGN KEY {foreign_key_name}

Example

Supose we have table called “product”

SHOW CREATE TABLE `product`

Result could look like below:

CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gallery_id` int(11) DEFAULT NULL,
`manufacturer_id` int(11) DEFAULT NULL,
`name` longtext COLLATE utf8_unicode_ci NOT NULL,
`description` longtext COLLATE utf8_unicode_ci,
`price` decimal(10,2) NOT NULL,
`vat` int(11) NOT NULL,
`sku` longtext COLLATE utf8_unicode_ci,
`crdate` datetime NOT NULL,
`deleted` datetime DEFAULT NULL,
`hidden` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_6CB28F474E7AF8F` (`gallery_id`),
KEY `IDX_6CB28F47A23B42D` (`manufacturer_id`),
CONSTRAINT `FK_6CB28F474E7AF8F` FOREIGN KEY (`gallery_id`) REFERENCES `cms__gallery` (`id`),
CONSTRAINT `FK_6CB28F47A23B42D` FOREIGN KEY (`manufacturer_id`) REFERENCES `product__manufacturer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=118 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

We see few constraints: “FK_6CB28F474E7AF8F” and “FK_6CB28F47A23B42D”.

Now we can drop them.

ALTER TABLE `product` DROP FOREIGN KEY FK_6CB28F474E7AF8F
ALTER TABLE `product` DROP FOREIGN KEY FK_6CB28F47A23B42D

Comments are closed.

Still not convinced?

Contact Us and tell what You need.

We will prepare a competitive offer and send you examples of our previous projects.

Twitter
  • Tweets not available at this time. Please Try again later
Toys we play with
Get in touch
  • Address:

    Nextima Sp. z o.o. ul. Terespolska 4 / 375 03813 WARSAW, POLAND

  • Email

    contact@nextima.com

  • Phone

    (+48) 501 32 40 41