Some times we have to drop the foreign key constraint that already exists, for that we have to first get the foreign key constraint name.
To see detailed description of created table we use following query.
1 | SHOW CREATE TABLE location; |
Result of above query is
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | mysql> SHOW CREATE TABLE location; +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----+ | Table | Create Table | +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----+ | location | CREATE TABLE `location` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `address` varchar(200) DEFAULT NULL, `company_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `company_fk_id` (`company_id`), CONSTRAINT `company_fk_id` FOREIGN KEY (`company_id`) REFERENCES `company` (`i d`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----+ 1 row in set (0.08 sec) |
Here we can see that company_id column is referring company tables id field with the constraint name company_fk_id.
Now we have to drop this constraint for that we use ALTER TABLE as follows
Syntax
1 2 | ALTER TABLEL table_name DROP FOREIGN KEY fk_symbol; |
Here fk_symbol is foreign key constraint used during table creation here we given company_fk_id. If not given then it is system generated value.
1 | ALTER TABLE location DROP FOREIGN KEY company_fk_id; |
Again checking show create table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | mysql> show create table location; +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------+ | Table | Create Table | +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------+ | location | CREATE TABLE `location` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `address` varchar(200) DEFAULT NULL, `company_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `company_fk_id` (`company_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------+ 1 row in set (0.00 sec) |
So we have successfully removed foreign key reference.