Why can't I drop a foreign key?

Question

Scenario:

Parent table | id primary key, message_p
Child  table | id primary key, parent_id foreign key, message_c

I had 1 row of data in the parent table and 2 rows of data in the child table. I wanted to test constraints that an FK relationship enforces. I then attempted to remove the foreign key from the child table so that evene though the child table had 2 rows, I could then go ahead and remove the parent row:

alter table child 
drop foreign key parent_id

I then got the following error:

[1091 - Can't DROP 'parent_id'; check that column/key exists]

Notes:

show create table child

CREATE TABLE `track` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `member_id` int(11) NOT NULL,
 `title` varchar(50) DEFAULT NULL,
 `artist` varchar(50) DEFAULT 'TBA',
 `album` varchar(50) DEFAULT 'TBA',
 `genre` varchar(50) DEFAULT 'TBA',
 `dance_style` varchar(50) DEFAULT 'TBA',
 PRIMARY KEY (`id`),
 KEY `member_id` (`member_id`),
 CONSTRAINT `track_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Am I missing something in my query or the general understanding about FK's?

Answer 1

You are trying to delete the Foreign Key Constraint by column name, that's why your code doesn't work.

First query your foreign key constraint name (using show create table child as you did show the key name, something like track_ibfk_1

Answer 2

If you tried out everything as commented (assuming correct table names, constraint names, ...), I see no reason why it should not work.

If you have, however, other tables that hold foreign keys to parent (or 'member'), maybe that these constraints block removal of parent entries?

Anyway, here is an example showing that dropping a foreign key actually works:

drop table if exists  testchild;
drop table if exists  test;

create table test(
id int primary key,
name varchar(50)
);

create table testchild(
childid int primary key,
reftotest int,
constraint reftotest_FK foreign key (reftotest) references test(id)
);

insert into test values (1, 'Jack'),  (2, 'Sam');
insert into testchild values (1, 1), (2, 2), (3, 1);

insert into testchild values (4,5); # will fail
delete from test where id = 1; # will fail

alter table testchild drop foreign key reftotest_FK;
insert into testchild values (4,5); # will not fail any more
delete from test where id = 1; # will not fail any more