[MDEV-32171] When updating cascaded foreign keys, closing foreign key constraints is ignored and invalid. Created: 2023-09-14  Updated: 2023-12-11  Resolved: 2023-12-11

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 11.1.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Runzi Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: foreign-keys
Environment:

Docker containers with mariadb-galera-cluster
Mariadb version: 11.1.2

Issue Links:
Relates
relates to MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTR... Open
relates to MDEV-31086 MODIFY COLUMN can break FK constraint... Closed
relates to MDEV-31987 Cannot disable FOREIGN_KEY_CHECKS any... Closed

 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]>



 Comments   
Comment by Runzi [ 2023-09-14 ]

I am not sure whether this is expected or a bug. maybe my approach is incorrect?

Comment by Marko Mäkelä [ 2023-09-15 ]

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:

  1. drop the foreign key constraint and modify the column in the child table
  2. modify the column in the (former) parent table
  3. 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.

Comment by Runzi [ 2023-09-19 ]

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?

Comment by Marko Mäkelä [ 2023-09-21 ]

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.

Comment by Runzi [ 2023-09-25 ]

I see, thank you for your reply

Generated at Thu Feb 08 10:29:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.