MySQL find out foreign key constraint on column

Question

I am trying to do:

 ALTER TABLE keycloak_prod.KEYCLOAK_ROLE CHANGE APP_REALM_CONSTRAINT CLIENT_REALM_CONSTRAINT VARCHAR(36)

But I get:

SQL-Error [1846] [0A000]: ALTER TABLE is not supported. Reason: Altering name of a field being referenced from a foreign key is not supported. Try dropping foreign key first.

When trying out to find out FK constraints on that column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = (SELECT DATABASE()) AND
  REFERENCED_TABLE_NAME = 'KEYCLOAK_ROLE'

I only get:

TABLE_NAME          |COLUMN_NAME|CONSTRAINT_NAME             |REFERENCED_TABLE_NAME|REFERENCED_COLUMN_NAME|
--------------------+-----------+----------------------------+---------------------+----------------------+
COMPOSITE_ROLE      |COMPOSITE  |FK_A63WVEKFTU8JO1PNJ81E7MCE2|KEYCLOAK_ROLE        |ID                    |
COMPOSITE_ROLE      |CHILD_ROLE |FK_GR7THLLB9LU8Q4VQA4524JJY8|KEYCLOAK_ROLE        |ID                    |
REALM_DEFAULT_ROLES |ROLE_ID    |FK_H4WPD7W4HSOOLNI3H0SW7BTJE|KEYCLOAK_ROLE        |ID                    |
SCOPE_MAPPING       |ROLE_ID    |FK_P3RH9GRKU11KQFRS4FLTT7RNQ|KEYCLOAK_ROLE        |ID                    |
CLIENT_DEFAULT_ROLES|ROLE_ID    |FK_8AELWNIBJI49AVXSRTUF6XJOW|KEYCLOAK_ROLE        |ID                    |

So the column which I am trying to rename does not appear here.

SHOW CREATE TABLE:

Table        |Create Table                                                                                                                                                                                                                                                   |
-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
KEYCLOAK_ROLE|CREATE TABLE `KEYCLOAK_ROLE` (¶  `ID` varchar(36) NOT NULL,¶  `APP_REALM_CONSTRAINT` varchar(36) DEFAULT NULL,¶  `CLIENT_ROLE` bit(1) DEFAULT NULL,¶  `DESCRIPTION` varchar(255) DEFAULT NULL,¶  `NAME` varchar(255) DEFAULT NULL,¶  `REALM_ID` varchar(255) DE|

When In DBeaver I generate the DDL for the table, it shows me:

-- keycloak_prod.KEYCLOAK_ROLE definition

CREATE TABLE `KEYCLOAK_ROLE` (
  `ID` varchar(36) NOT NULL,
  `APP_REALM_CONSTRAINT` varchar(36) DEFAULT NULL,
  `CLIENT_ROLE` bit(1) DEFAULT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `REALM_ID` varchar(255) DEFAULT NULL,
  `CLIENT` varchar(36) DEFAULT NULL,
  `REALM` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_J3RWUVD56ONTGSUHOGM184WW2` (`NAME`,`APP_REALM_CONSTRAINT`),
  KEY `FK_6VYQFE4CN4WLQ8R6KT5VDSJ5C` (`REALM`),
  KEY `FK_PIMO5LE2C0RAL09FL8CM9WFW9` (`CLIENT`),
  CONSTRAINT `FK_6VYQFE4CN4WLQ8R6KT5VDSJ5C` FOREIGN KEY (`REALM`) REFERENCES `REALM` (`ID`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Answer 1

You can achieve this by doing the following:

1. Drop the Foreign Key Constraint Temporarily.
2. Drop the Unique Constraint Temporarily.
3. Rename the Column.
4. Re-add the Constraints.

---

SQL workflow:

-- Drop the unique constraint temporarily
ALTER TABLE keycloak_prod.KEYCLOAK_ROLE DROP INDEX UK_J3RWUVD56ONTGSUHOGM184WW2;

-- Attempt to rename the column again
ALTER TABLE keycloak_prod.KEYCLOAK_ROLE CHANGE APP_REALM_CONSTRAINT CLIENT_REALM_CONSTRAINT VARCHAR(36);

-- Re-add the unique constraint with the new column name
ALTER TABLE keycloak_prod.KEYCLOAK_ROLE ADD UNIQUE UK_J3RWUVD56ONTGSUHOGM184WW2 (`NAME`, `CLIENT_REALM_CONSTRAINT`);