Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.1.2
-
Docker containers with mariadb-galera-cluster
Mariadb version: 11.1.2
Description
table1:
CREATE TABLE users (
|
id INT PRIMARY KEY,
|
name VARCHAR(50) |
);
|
table2
CREATE TABLE orders (
|
id INT PRIMARY KEY,
|
user_id INT,
|
order_date DATE,
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
);
|
Update:
ALTER TABLE users MODIFY COLUMN `id` tinyint(11) unsigned NOT NULL auto_increment; |
ERROR 1833 (HY000): Cannot change column 'id': used in a foreign key constraint 'orders_ibfk_1' of table 'runzi.orders' |
|
|
|
|
MariaDB [runzi]> SET FOREIGN_KEY_CHECKS=0; |
Query OK, 0 rows affected (0.001 sec) |
|
|
MariaDB [runzi]> ALTER TABLE users MODIFY COLUMN `id` tinyint(11) unsigned NOT NULL auto_increment; |
ERROR 1833 (HY000): Cannot change column 'id': used in a foreign key constraint 'orders_ibfk_1' of table 'runzi.orders' |
MariaDB [runzi]>
|
Attachments
Issue Links
- relates to
-
MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTRAINT
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
Activity
MDEV-31086 changed this. Let me partially quote my comment there:
It turns out in the SQL layer, the function fk_check_column_changes() would intentionally cause ALTER TABLE…ALGORITHM=COPY to ignore invalid foreign key constraints if foreign_key_checks=OFF. This would seem to be misguided, because a subsequent TRUNCATE TABLE or OPTIMIZE TABLE (if alter_algorithm=copy) and possibly ALTER TABLE…FORCE, ALGORITHM=COPY would be refused on the table.
When foreign_key_checks=OFF, I think that it is reasonable to allow CREATE TABLE or ALTER TABLE to create FOREIGN KEY constraints that refer to non-existing tables. But I do not see the usefulness of allowing the creation of totally invalid foreign key constraints, that is, when columns are of incompatible type or use incompatible character encodings or collations.
I think a good guideline is that the output of SHOW CREATE TABLE after any number of DDL operations will be a valid CREATE TABLE statement if foreign_key_checks=OFF.
If someone wants to change the collation or encoding of a column that is connected to a FOREIGN KEY constraint, that can be done with 3 ALTER TABLE statements.
In MDEV-31987 you can find an example of using 3 ALTER TABLE statements:
- drop the foreign key constraint and modify the column in the child table
- modify the column in the (former) parent table
- add the constraint back to the child table
The benefit of using foreign_key_checks=off while doing this is that that the constraint can be added instantly, without any validation. Currently (until MDEV-16356 is fixed), the validation involves an unnecessary table rebuild.
thanks, therefore, when foreign_key_checks=OFF is used, only the check of add/del foreign key constraint is ignored, instead of the check of modification or update. If I need to update, I can only implement it by deleting/adding logic, right?
Is this a long-term solution?
I do not foresee any changes to this logic in the near term. Implementing transactional DDL (so that the 2 ALTER TABLE could be executed as one atomic unit) would be a huge undertaking and could be years ahead.
I am not sure whether this is expected or a bug. maybe my approach is incorrect?