2

I have a MySQL database And I want to add a column:

MariaDB [(none)]> use myDatabase;
Database changed
MariaDB [myDatabase]>

ALTER TABLE material add new_column FLOAT;

But I get the following error:

ERROR 1054 (42S22): Unknown column '`myDatabase`.`m`.`existing_column`' in 'CHECK'

Sure enough, the existing_column is in the table material:

MariaDB [myDatabase]> describe material;                                                         
+------------------------------+--------------+------+-----+---------+----------------+       
| Field                        | Type         | Null | Key | Default | Extra          |       
+------------------------------+--------------+------+-----+---------+----------------+       
| id                           | int(11)      | NO   | PRI | NULL    | auto_increment |       
| existing_column              | tinyint(1)   | YES  |     | NULL    |                |       
+------------------------------+--------------+------+-----+---------+----------------+       
42 rows in set (0.003 sec)

(i've left out the other columns for clarity)

And there is a CHECK constraint in place:

MariaDB [myDatabase]> SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE `TABLE_NAME` = "material";
+--------------------+-------------------+------------+-----------------+-------------------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | CHECK_CLAUSE                        |
+--------------------+-------------------+------------+-----------------+-------------------------------------+
| def                | myDatabase        | material   | CONSTRAINT_1    | `existing_column` in (0,1)          |
+--------------------+-------------------+------------+-----------------+-------------------------------------+
2 rows in set (0.007 sec)

I've tried:

  • Making sure all values in existing_column are either 0 or 1 -> No change
  • Dropping the CHECK -> I just get the same error when I try:
MariaDB [myDatabase]> alter table material drop constraint CONSTRAINT_1;
ERROR 1054 (42S22): Unknown column '`myDatabase`.`m`.`existing_column`' in 'CHECK'
  • making an sqldump and importing it on another system -> No error and I can add my column!

Context:

  • I'm using mysql 10.3.29 on Debian 10
  • I normally use flask-sqlalchemy and flask-migrate for managing migrations. That's where I got the error initially.
  • I don't really need the CHECK constraint. Sqlalchemy added it automatically
O. Jones
  • 103,626
  • 17
  • 118
  • 172
snin
  • 43
  • 5
  • In stead of `describe material;`, you should do `SHOW CREATE TABLE material;` because this also show existing constraints. – Luuk Feb 20 '22 at 14:04

1 Answers1

3

Linking this issue here because it's similar: MariaDB: ALTER TABLE command works on one table, but not the other

I was running MariaDB on Debian: 10.5.10-MariaDB-1:10.5.10+maria~buster

I could apply schema to other databases, but I was getting stuck on one table that kept raising the same error:

ERROR 1054 (42S22): Unknown column '`database`.`table`.`col`' in 'CHECK'

Updating MariaDB to 10.5.15 allowed me to apply the schema. It might have just needed a restart - but impossible to know now.

illusional
  • 51
  • 3