Search code examples
mariadbalter-table

Key column X doesn't exist in table while trying to drop X from table

I need some help. I'm trying to run some migrations scripts on a NEW docker instance (the old one and local works fine). Mariadb, Innodb engine.

I have a migrations like this:

ALTER TABLE userinfo DROP COLUMN barcode

that gives me error: StatementEnd Error Code: 1072. Key column 'barcode' doesn't exist in table

The column exist! This is my SHOW CREATE TABLE:

CREATE TABLE userinfo (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  barcode varchar(45) NOT NULL,
  store_id int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY store_id_barcode_UNIQUE (`store_id`,`barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

I tried something like ALTER TABLE userinfo ADD KEY "barcode" ("barcode") but the DROP statement still does not work.

P.S. i removed a lot of columns from the CREATE statement or this thread would be too long to read.

Solution

  • From ALTER TABLE/DROP COLUMN:

    MariaDB starting with 10.2.8
    Dropping a column that is part of a multi-column UNIQUE constraint is not permitted...

    First, drop the unique key in which barcode is part of and then you will be able to drop it:

    ALTER TABLE userinfo DROP KEY store_id_barcode_UNIQUE;
ALTER TABLE userinfo DROP COLUMN barcode;

    See the demo.