phpmyadmin / phpmyadmin/phpmyadmin
Foreign key check setting is not respected
Nobody has claimed this yet.
- Dominant language
- PHP
- Stars
- 7.9k
- Forks
- 3.6k
- Avg merge
- 4d 18h
- Merged PRs (30d)
- 36
Description
Describe the bug
The setting for foreign key checks has no effect. I checked the phpMyAdmin configuration and it's set to Server default which is:
SHOW VARIABLES;
// ...
foreign_key_checks | ON
I changed the phpMyAdmin setting for the current session to Enable but it's still the same behaviour.
When executing a DROP INDEX query via STRUCTURE tab -> Indexes -> Drop the following form data are sent:
server: 1
table: test_foreign_key
db: test
goto: tbl_structure.php
sql_query: ALTER TABLE `test_foreign_key` DROP INDEX `FOREIGN_KEY`;
message_to_show: Index FOREIGN_KEY has been dropped.
is_js_confirmed: 1
ajax_request: true
fk_checks: 0
_nocache: 1582562296948961610
token: 31607d473d5c724c4b6d3e2d75417562
Furthermore I tried executing the query via the SQL tab. The Enable foreign key checks checkbox is checked. The form data look different, especially the duplicate fk_checks key looks suspicious:
is_js_confirmed: 1
db: test
table: test_foreign_key
token: 31607d473d5c724c4b6d3e2d75417562
pos: 0
goto: tbl_sql.php
message_to_show: Your SQL query has been executed successfully.
prev_sql_query:
sql_query: ALTER TABLE `test_foreign_key` DROP INDEX `FOREIGN_KEY`
sql_delimiter: ;
fk_checks: 0
fk_checks: 1
SQL: Go
ajax_request: true
ajax_page_request: true
_nocache: 1582562484931112474
token: 31607d473d5c724c4b6d3e2d75417562
To Reproduce
Two test tables:
CREATE TABLE `test_entity` (
`ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE `test_foreign_key` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`FOREIGN_KEY` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`ID`),
KEY `FOREIGN_KEY` (`FOREIGN_KEY`),
CONSTRAINT `test_foreign_key_ibfk_1` FOREIGN KEY (`FOREIGN_KEY`) REFERENCES `test_entity` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Steps to reproduce the behavior:
- Go to Table Structure
- Click on "Drop" of "FOREIGN_KEY" index
- A modal opens, confirm it
Expected behavior
It's not allowed to drop the index because it's used in a foreign key constraint.
Screenshots

Server configuration
- Operating system: Debian
- Web server: Apache 2.4
- Database version: 5.6
- PHP version: 7.4.1
- phpMyAdmin version: 5.0.1
Client configuration
- Browser: Chrome (80.0.3987.116)
- Operating system: Ubuntu 18.04
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Reproduce the issue from the Structure tab by dropping the FOREIGN_KEY index on the supplied test tables, then compare the submitted fk_checks values with the SQL tab behavior. Trace the Structure > Indexes > Drop and SQL tab entry points to determine why foreign-key checks are not respected. Done means the drop is rejected when the index is required by the foreign-key constraint.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mysql, php
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100