MySQL 8.0

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