how to delete foreign key in mysql

Question

Am using mysql and trying to drop foreign constraint, but i can't to delete that key.

SHOW CREATE TABLE xxxx;

its shows,

CREATE TABLE `xxxx` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `name` text NOT NULL,
 `article_title` text NOT NULL,
 `created_at` datetime NOT NULL,
 `last_modified_at` datetime NOT NULL,
 `latest_version` tinyint(4) NOT NULL,
 `status` tinyint(4) NOT NULL,
 `is_deleted` enum('0','1') NOT NULL,
 `deleted_time` datetime NOT NULL,
 `manual_authorgroup_data` text NOT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `fk_users_xxxx_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1

How to fix this issue, please help me.

http://stackoverflow.com/questions/14122031/how-to-remove-constraints-from-my-mysql-table — Marco, Oct 24 '16 at 10:26
@GoudaElalfy #1553 - Cannot drop index 'user_id': needed in a foreign key constraint — Mansoor H, Oct 24 '16 at 10:29
@Marco its shows, `#1091 - Can't DROP 'user_id'; check that column/key exists` — Mansoor H, Oct 24 '16 at 10:31
You should not drop user_id but fk_users_xxxx_user_id, but you must be aware that you will be able to create articoles for users that do not exist — Marco, Oct 24 '16 at 10:33
Possible duplicate of [How to remove constraints from my MySQL table?](http://stackoverflow.com/questions/14122031/how-to-remove-constraints-from-my-mysql-table) — Bardi Harborow, Feb 15 '17 at 05:56

score 1 · Answer 1 · answered Oct 24 '16 at 12:55

1

Try this,

ALTER TABLE `xxxx`
DROP FOREIGN KEY'fk_users_xxxx_user_id'

answered Oct 24 '16 at 12:55

suguna

781
5
14

select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = '’; You can use this syntax to know the foreign key name. Then try to drop.
– suguna Oct 24 '16 at 13:15
REFERENCED_TABLE_NAME means?? – Mansoor H Oct 24 '16 at 13:18
The table referenced by foreign key. In your case, it is 'users'. – suguna Oct 24 '16 at 13:19

how to delete foreign key in mysql

1 Answers1