mySQL Deleteing Foreign Key

Question

I have a database, I used these commands :

CREATE TABLE APPOINTMENT (app_id INT(5) NOT NULL, app_doctor INT(5) NOT NULL, app_date DATE, PRIMARY KEY (app_id));

CREATE TABLE APPPATIENTS (patient_ssn INT(10) NOT NULL, patient_name VARCHAR(20) NOT NULL, sex CHAR(1) NOT NULL, appointment_id INT(5), PRIMARY KEY (patient_ssn), UNIQUE (appointment_id));

ALTER TABLE APPPATIENTS ADD FOREIGN KEY (appointment_id) REFERENCES APPOINTMENT(app_id) ON DELETE CASCADE;

Now, I want to delete foreign key appointment_id in appPatients table :

ALTER TABLE apppatients DROP FOREIGN KEY appointment_id;

But I got an error :

Can't drop 'appointment_id'; check that column/key exists.

It is there! How can it give that error? What am I doing wrong?

do this - 'SHOW KEYS IN apppatients' and watch result - may be you delete this foreign key in the past. — degr, Apr 10 '15 at 13:37
@degr I did what you said but my foreign key is still there. — berkc, Apr 10 '15 at 13:38
and this one - alter table apppatients drop constraint appointment_id — degr, Apr 10 '15 at 13:41
@degr foreign key name is appointment_id, and I tried other command you said but I got a syntax error. — berkc, Apr 10 '15 at 13:43
lol, I don't know. You query look good. http://stackoverflow.com/questions/838354/mysql-removing-some-foreign-keys — degr, Apr 10 '15 at 14:44

score 0 · Answer 1 · answered Apr 10 '15 at 14:57

I tried to create a database with your code and I notice the foreign key name appears different from appointment_id: reading with more attention the command definition, I notice that when you write

ADD FOREIGN KEY (appointment_id)

you are only saying the column on which the constraint will be created, not its name.
You can read the specifications here: SQL FOREIGN KEY Constraint on ALTER TABLE

So, if you want to specify the name of the key, you have to write, for example:

alter table APPPATIENTS 
ADD CONSTRAINT appointment_key 
FOREIGN KEY (appointment_id) 
REFERENCES appointment(app_id) ON DELETE CASCADE;

And so you know the name of the foreign key you created (appointment_key in this case).
If you want to know the foreign key name generated by mysql you can use the command

show create table APPPATIENTS;

that shows the complete table definition.

mySQL Deleteing Foreign Key

1 Answers1