MySQL – Drop foreign key constraint

admin

March 15, 2011

[adsense id=”0514458240″ width=”468″ height=”60″]

In MySQL there is no single command that returns the constraints existing on a table other than using the SHOW CREATE TABLE command. This command provides the SQL necessary to recreate the table. The create statement will also include the names of the referential and primary key constraints. Using the ALTER TABLE command these can then be dropped.

Here is an example:

mysql> CREATE TABLE parent (id INT NOT NULL,
    ->                      PRIMARY KEY (id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE child (id INT, parent_id INT,
    ->                     INDEX par_ind (parent_id),
    ->                     FOREIGN KEY (parent_id) REFERENCES parent(id)
    ->                       ON DELETE CASCADE
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table child;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                     |
+-------+---------------------------------------------------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
  `id` int(11) default NULL,
  `parent_id` int(11) default NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table child drop foreign key child_ibfk_1;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL