By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id INT PRIMARY KEY, -- PK index created
val1 INT UNIQUE, -- unique index created
val2 INT, -- index and constraint created
FOREIGN KEY (val2) REFERENCES test (val1));
SHOW CREATE TABLE test
| Table | Create Table |
|---|---|
| test | CREATE TABLE `test` ( `id` int NOT NULL, `val1` int DEFAULT NULL, `val2` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `val1` (`val1`), KEY `val2` (`val2`), CONSTRAINT `test_ibfk_1` FOREIGN KEY (`val2`) REFERENCES `test` (`val1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
ALTER TABLE test DROP CONSTRAINT `test_ibfk_1` -- constraint dropped
-- index stays unchanged
Records: 0 Duplicates: 0 Warnings: 0
SHOW CREATE TABLE test
| Table | Create Table |
|---|---|
| test | CREATE TABLE `test` ( `id` int NOT NULL, `val1` int DEFAULT NULL, `val2` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `val1` (`val1`), KEY `val2` (`val2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |