-1

I want to alter a table called mvp to add a column. When I run the alter statement, I get an index error:

**ERROR 1553 (HY000):** Cannot drop index '<unknown key name>': needed in a foreign key constraint

I attempted workarounds listed on Stack Overflow. Each one eventually results the same 1553 index error. An answer on MySQL Cannot drop index needed in a foreign key constraint is to add a new index on the mvp table that includes the columns used by the foreign key. This fails with the index error.

I tried to SET FOREIGN_KEY_CHECKS=0; before the running the alter statement. I could not drop the foreign key constraints on a table related to mvp. Both fail with the index error.

The tables are:

CREATE TABLE `mvp` (
  `mvp_idtype_id` varchar(12) NOT NULL,
  `idtype` int NOT NULL,
  `id` int NOT NULL,
  `first_name` varchar(75) NOT NULL,
  `mid_name` varchar(75) DEFAULT NULL,
  `last_name` varchar(75) NOT NULL,
  `donation_bb_id` int DEFAULT NULL,
  `donation_report_to` varchar(75) DEFAULT NULL,
  `donation_nok_name1` varchar(75) DEFAULT NULL,

  PRIMARY KEY (`mvp_idtype_id`),
  KEY `mvp_pk` (`idtype`,`id`)
-- note: no unique key on idtype, id existed when the table was created.
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'

Table m_strk has a foreign key constraint that I cannot drop:

CREATE TABLE `m_strk` (
  `mvp_idtype_id` varchar(12) ,
  `idtype` int NOT NULL,
  `id` int NOT NULL,
  `sno` int NOT NULL,
  `sdate` date DEFAULT NULL,
  `add_date` datetime DEFAULT CURRENT_TIMESTAMP,
  KEY `m_strk_pk` (`idtype`,`id`),
  CONSTRAINT `m_strk_fk` FOREIGN KEY (`idtype`, `id`) REFERENCES `mvp` (`idtype`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'

ALTER TABLE mvp
ADD donation_nok_last_verified DATE DEFAULT NULL NULL
AFTER add_date;

**ERROR 1553 (HY000):** Cannot drop index '<unknown key name>': needed in a foreign key constraint

Drop the FK on m_strk:

Error Code: 1553. Cannot drop index '<unknown key name>': needed in a foreign key constraint

The table was created prior to upgrading to MySQL 8.4, and creating a non-unique key on idtype and id was permitted; so was having a foreign key reference those columns.

I can successfully create the m_strk table under MySQL 8.0 https://dbfiddle.uk/SnTlWXzk

The same code under 8.4 errors. https://dbfiddle.uk/r_hTeq1j

Improve this question
7
  • 1
    Please ask 1 specific researched non-duplicate question. Either ask re 1 unexpected query result with obligatory minimal reproducible example, including why you think it should return something else or are unsure at the 1st subexpression where you don't get what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal, giving working parts you can do with justification & a minimal reproducible example--then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. How to Ask Help center Basic questions are faqs. Commented May 7 at 23:37
  • 1
    A minimal reproducible example includes: cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS It is not helpful to just mention another post, you need to say what is relevant & why. (As with your 1st link but not your 2nd.) Commented May 7 at 23:47
  • 1
    Non-deleted question URLs are expanded automatically to their current question title (regardless of the title text in the URL). Answer URLs are not expanded Acceptance can change, give answer links. Comments are ephemeral, avoid comment links in posts; when quoting give credit to user profile. Give credit in always-visible text. Commented May 8 at 0:00
  • A minimal reproducible example is code that you have run from a copy & paste from your post to confirm that it exhibits the relevant behavioiur. Commented May 8 at 4:55
  • 1
    you have left off the unique key on mvp that supports the foreign key in m_strk. this makes it impossible to even attempt to duplicate your problem. dbfiddle.uk/GrvRB8qr Commented May 8 at 12:55

1 Answer 1

Reset to default
1

Apart from the table structure of mvp and m_strk, which both have a design flaw caused by incorrect sequence of columns, your code runs fine for me.

Let's address the design problem.

CREATE TABLE `mvp` (
  `mvp_idtype_id` varchar(12) NOT NULL DEFAULT (concat(`idtype`,_utf8mb4'-',lpad(`id`,4,_utf8mb4'0'))),
  `idtype` int NOT NULL,
  `id` int NOT NULL,

  `first_name` varchar(75) NOT NULL,
  `mid_name` varchar(75) DEFAULT NULL,
  `last_name` varchar(75) NOT NULL,
  `donation_bb_id` int DEFAULT NULL,
  `donation_report_to` varchar(75) DEFAULT NULL,
  `donation_nok_name1` varchar(75) DEFAULT NULL,

  PRIMARY KEY (`mvp_idtype_id`),
  KEY `mvp_pk` (`idtype`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='N';

Apart from the ENCRYPTION attribute which is set to N due to the lack of master key, the table can be created exactly like the original one. However, its primary key mvp_idtype_id has a default value which is based on columns (idtype and id) AFTER it. This will cause the default value fail to acquire values from those base columns as their values have yet to be determined.

insert mvp values 
(default,1,1,'a','b','c',1,'aa','bb'),
(default,2,2,'a2','b2','c2',2,'aa2','bb2');

ERROR 1062 (23000): Duplicate entry '0-0000' for key 'mvp.PRIMARY'

Unless you change the table structure to place the PK after the said columns, or change the value sequence in the INSERT statement, the PK cannot procure a correct default value. Here is an example of INSERT statement with arranged value sequence.

insert mvp (idtype,id,mvp_idtype_id,first_name,mid_name,last_name,donation_bb_id,donation_report_to,donation_nok_name1) values
(1,1,default,'a','b','c',1,'aa','bb'),
(2,2,default,'a2','b2','c2',2,'aa2','bb2'); 

select * from mvp;
+---------------+--------+----+------------+----------+-----------+----------------+--------------------+--------------------+
| mvp_idtype_id | idtype | id | first_name | mid_name | last_name | donation_bb_id | donation_report_to | donation_nok_name1 |
+---------------+--------+----+------------+----------+-----------+----------------+--------------------+--------------------+
| 1-0001        |      1 |  1 | a          | b        | c         |              1 | aa                 | bb                 |
| 2-0002        |      2 |  2 | a2         | b2       | c2        |              2 | aa2                | bb2                |
+---------------+--------+----+------------+----------+-----------+----------------+--------------------+--------------------+

Now we create the second table.

CREATE TABLE `m_strk` (
  `mvp_idtype_id` varchar(12) DEFAULT (concat(`idtype`,_utf8mb4'-',lpad(`id`,4,_utf8mb4'0'))),
  `idtype` int NOT NULL,
  `id` int NOT NULL,
  `sno` int NOT NULL,
  `sdate` date DEFAULT NULL,
  `add_date` datetime DEFAULT CURRENT_TIMESTAMP,
  KEY `m_strk_pk` (`idtype`,`id`),
  CONSTRAINT `m_strk_fk` FOREIGN KEY (`idtype`, `id`) REFERENCES `mvp` (`idtype`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='N';

Table m_strk was created with no problem. Again, the ENCRYPTION is set to 'N', and using of the DEFAULT value for the first column mvp_idtype_id calls for extra care even though it's not a PK in this table (Not being a PK makes the situation worse as there will be no constraint for the ill-gotten duplicate values).

Next we populate the table with valid rows.

insert m_strk (idtype,id,mvp_idtype_id,sno,sdate,add_date) values
(1,1,default,111,null,default),
(2,2,default,222,null,default);

select * from m_strk;
+---------------+--------+----+-----+-------+---------------------+
| mvp_idtype_id | idtype | id | sno | sdate | add_date            |
+---------------+--------+----+-----+-------+---------------------+
| 1-0001        |      1 |  1 | 111 | NULL  | 2025-05-08 11:51:18 |
| 2-0002        |      2 |  2 | 222 | NULL  | 2025-05-08 11:51:18 |
+---------------+--------+----+-----+-------+---------------------+

insert m_strk (idtype,id,mvp_idtype_id,sno,sdate,add_date) values 
(3,3,default,333,null,default);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`m_strk`, CONSTRAINT `m_strk_fk` FOREIGN KEY (`idtype`, `id`) REFERENCES `mvp` (`idtype`, `id`))

The last statement failed due to the FK check.

Then we add a new column using your code and drop the FK on m_strk.

ALTER TABLE mvp
ADD donation_nok_last_verified DATE DEFAULT NULL NULL
AFTER donation_report_to;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

alter table m_strk drop foreign key `m_strk_fk` ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Both statements ran successfully. So let's proceed with your final job of applying changes to m_strk to add the FK on m_strk referencing mvp. Again, you did not provide a statement, and also did not define how the FK should be built, so I have to write one assuming to add the previously dropped FK back.

alter table m_strk add foreign key (`idtype`, `id`) REFERENCES `mvp` (`idtype`, `id`);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

Why does adding a column to a table result in an index error and how can I work around it

No problem has been encountered using (primarily) your code.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

There's no space before punctuation in English.
Please avoid social & meta commentary in posts. If the question post requires editing, comment on it. If the question post doesn't ask 1 specific question it should be commented on for editing & not answered. How to Answer How to Ask Help center

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.