I
I
Ingco2019-11-18 11:54:37
MySQL
Ingco, 2019-11-18 11:54:37

How to remove FOREIGN KEY constraint between two tables in Mysql?

Unable to remove contraints in MariaDB.
1. Check for restrictions:

SHOW CREATE TABLE `patient_patientquestionnaireanswer`;

2. I get a response:

...
PRIMARY KEY (`id`),
KEY `constraint_name_1` (`column_1_id`) USING BTREE,
KEY `constraint_name_2` (`column_2_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=330 DEFAULT CHARSET=utf8

3. I also check:
select `CONSTRAINT_NAME`, `TABLE_NAME`, `COLUMN_NAME`, `REFERENCED_TABLE_NAME`, `REFERENCED_COLUMN_NAME`
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
where REFERENCED_TABLE_SCHEMA = 'my_db' and TABLE_NAME = 'table_with_contraints';

4. I get two lines in response. From them I take the value of `CONSTRAINT_NAME` and substitute it in the request:
ALTER TABLE `my_db`
    DROP FOREIGN KEY `constraint_name`;

5. I get an error:
Kernel Error: Error( 1091 ) 42000: "Can't DROP FOREIGN KEY `constraint_name`; check that it exists"

Where did I go wrong? How can I remove constraints so that the linked table can be modified?
* All column names or Contraint Key are correct. In the question, I indicated the names for the example.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2019-11-18
@tsklab

ALTER TABLE `table_with_contraints`
    DROP FOREIGN KEY `constraint_name`;

Even though the error says constraint_name_1. And maybe constraint_nameit's a column name, not a value.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question