CODEWITHSUNDEEP
mariadbmariadb-10.5
Bob
Bob

Reputation: 8714

Not able to drop field in MariaDB table

I have table my_transitions and I am trying to delete field called myStage.

I am using this command:

alter table `my_transitions` drop `myStage`

I am seeing this error:

Key column 'myStage' doesn't exist in table

But when I list all fields using this command: describe my_transitions

I can see

id  bigint(20) unsigned NO  PRI NULL    auto_increment
myStage varchar(255)    NO  MUL NULL    
updated_at  timestamp   YES     NULL

Anyone can see if I am doing something wrong?

EDIT:

If I run show create table my_transitions;, I am getting:

CREATE TABLE `my_transitions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `myStage` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `myStage1` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_stage_combination` (`myStage`,`myStage1`),
  KEY `my_transitions_myStage` (`myStage`),
  KEY `my_transitions_myStage1` (`myStage1`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Upvotes: 0

Views: 818

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562310

This is a pecular bug in MariaDB. It affects MariaDB 10.5.

Demo: https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=867204670347fa29e40bd5eb510c6956

The workaround is to drop the UNIQUE KEY that column mystage is part of first, then drop the column.

alter table my_transitions drop key unique_stage_combination, drop column mystage;

P.S.: I tested this on MySQL 8.0 and it does not require the workaround. It does drop the column, but it leaves the table with a UNIQUE KEY column on just one column mystage1, which might not be what you want.

Upvotes: 1

Bob
Bob

Reputation: 8714

I have solved it by first deleting the unique key ALTER TABLE my_transitions DROP INDEX unique_stage_combination;

It seems like it is not possible to delete a column if it is a part of index key in Maria DB 10.5.8.

Upvotes: 1

Related Questions