Как изменить ограничения внешнего ключа?
При вводе такой команды ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT; выдает такую ошибку ERROR 1833 (HY000): Cannot change column ‘person_id’: used in a foreign key constraint ‘fk_person_id’ of table ‘bank.favorite_food’
Следовательно как я понял мне нужно как то отменить ограничения внешнего ключа!! как это сделать я так и не нашел. Помогите!
-
Вопрос заданболее трёх лет назад
-
1738 просмотров
Пригласить эксперта
Во всех таблицах, ссылающихся на `person`.`person_id` убрать соответствующий внешний ключ, затем изменить тип колонки в `person` и в связанных таблицах, затем восстановить внешние ключи связанных таблиц.
-
Показать ещё
Загружается…
09 февр. 2023, в 14:22
1500 руб./за проект
09 февр. 2023, в 13:58
2000 руб./за проект
09 февр. 2023, в 13:28
777 руб./за проект
Минуточку внимания
You can turn off foreign key checks:
SET FOREIGN_KEY_CHECKS = 0;
/* DO WHAT YOU NEED HERE */
SET FOREIGN_KEY_CHECKS = 1;
and with AUTO_INCREMENT you might face issue and for that you can use:
ERROR 1062: ALTER TABLE causes auto_increment resequencing, resulting
in duplicate entry ‘1’ for key ‘PRIMARY’
get the current value of sql_mode by this query:
SELECT @@sql_mode;
Then set sql_mode to NO_AUTO_VALUE_ON_ZERO using below query:
SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';
Make sure to take backup before applying changes.
Because foreign keys must match the data type of the column they reference, it makes sense that changing the data type of a column is likely to upset the foreign key that references it.
It seems that MySQL has a safeguard against this, and denies an attempt to MODIFY the column. But it’s not smart enough to check whether the specific modification you’re making will in fact change the data type. It just denies any attempt to modify that column.
You can work around this by temporarily disabling foreign key checks. I recreated your table and confirmed it:
mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'favorite_food_ibfk_1' of table 'test.favorite_food'
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set foreign_key_checks=1;
Tags:
Mysql
Sql
Related
Я пытаюсь добавить автоматическое приращение к первичному ключу person_id таблицы людей. Однако, когда я запускаю команду, я получаю следующую ошибку.
ОШИБКА 1833 (HY000): невозможно изменить столбец «person_id»: используется в ограничении внешнего ключа «fk_fav_food_person_id» таблицы «bank.favorite_food»
Для следующей команды. Я использую MySQL 5.7.20.
mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
2 ответа
Лучший ответ
Поскольку внешние ключи должны соответствовать типу данных столбца, на который они ссылаются, имеет смысл, что изменение типа данных столбца может нарушить внешний ключ, который на него ссылается.
Кажется, что MySQL имеет защиту от этого и отвергает попытку ИЗМЕНИТЬ столбец. Но недостаточно умен, чтобы проверить, действительно ли конкретная модификация, которую вы делаете, изменит тип данных. Он просто отрицает любую попытку изменить этот столбец.
Вы можете обойти это, временно отключив проверку внешнего ключа. Я воссоздал вашу таблицу и подтвердил ее:
mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'favorite_food_ibfk_1' of table 'test.favorite_food'
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set foreign_key_checks=1;
13
Bill Karwin
7 Янв 2018 в 18:45
Вы можете отключить проверку внешнего ключа:
SET FOREIGN_KEY_CHECKS = 0;
/* DO WHAT YOU NEED HERE */
SET FOREIGN_KEY_CHECKS = 1;
И с AUTO_INCREMENT вы можете столкнуться с проблемой, и для этого вы можете использовать:
ОШИБКА 1062: ALTER TABLE вызывает изменение последовательности auto_increment, что приводит к дублированию записи «1» для ключа «PRIMARY».
Получить текущее значение sql_mode по этому запросу:
SELECT @@sql_mode;
Затем установите sql_mode в NO_AUTO_VALUE_ON_ZERO, используя следующий запрос:
SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';
Перед применением изменений обязательно сделайте резервную копию.
0
Nirav Mehta
26 Фев 2020 в 18:16
Recently, I received a support request where the customer wanted to convert an INTEGER column to BIGINT on two tables. These tables are related by a foreign key, and it is a 3 node PXC cluster. These tables are 20GB and 82 GB in size and DDL’s on such tables in a production environment is always a challenge. We have options like direct ALTER or using pt-online-schema-change to get this done, but this is a very special case where none of them will be able to do this DDL. To demonstrate why this is so, please follow the table schema and the example below about DDL queries on foreign key columns. In the end, I will discuss an easy workaround, too.
Please take a look at table schema before reading further. In the below schema, the product_catalog_id column from the product_details table refers to catalog_id column from the product_catalog table. Both these fields are INT(11) and the customer wanted to convert them to BIGINT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> show create table product_catalog G *************************** 1. row *************************** Table: product_catalog Create Table: CREATE TABLE `product_catalog` ( `catalog_id` int(11) unsigned NOT NULL AUTO_INCREMENT, ..... PRIMARY KEY (`catalog_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) mysql> show create table product_details G *************************** 1. row *************************** Table: product_details Create Table: CREATE TABLE `product_details` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `product_catalog_id` int(11) unsigned NOT NULL, ..... PRIMARY KEY (`product_id`), KEY `fk_audit_detail_audit_header_idx` (`product_catalog_id`), CONSTRAINT `product_catalog_ibfk_1` FOREIGN KEY (`product_catalog_id`) REFERENCES `product_catalog` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) |
Changing a column from INT to BIGINT is an ONLINE ALTER. But in this case, it is not an ONLINE ALTER as the primary key is defined in this column. I started with a direct ALTER and later with pt-online-schema-change. Let’s have a look at how these tools react to this DDL change.
Direct Alter:
Considering the tables are close to 100GB in size together, a direct ALTER is not a good choice especially with a PXC cluster, and also on a standard deployment, it would block queries on metadata lock. But let’s see how the direct ALTER reacts here. I will first alter the child table and then the parent table.
|
mysql> ALTER TABLE product_details MODIfY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL ; ERROR 1832 (HY000): Cannot change column ‘product_catalog_id’: used in a foreign key constraint ‘product_details_ibfk_1’ |
It failed with Error 1832. Let’s try changing the column in the parent table first.
|
mysql> ALTER TABLE product_catalog MODIfY COLUMN catalog_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ; ERROR 1833 (HY000): Cannot change column ‘catalog_id’: used in a foreign key constraint ‘product_details_ibfk_1’ of table ‘DB255525.product_details’ |
This time, it is Error 1833. Both these errors state that it cannot change the column involved in a foreign key. The reason is that a foreign key is defined only between two identical columns. Changing any of those column data types would result in an error.
pt-online-schema-change:
It is always recommended to use pt-online-schema-change for DDL’s in PXC cluster deployments, provided additional disk space is available. You can refer to this blog post to know when to use this tool. pt-osc works by creating a new table with the required change in place, and copies data to the new table. The challenge comes in when there are child tables referring some column in this parent table. Presence of foreign keys complicates the job of pt-osc.
There are two ways the tool handles the foreign key constraints on the child table when the parent table is renamed as part of pt-osc. Below explains each of those cases.
alter-foreign-keys-method=auto
|
# pt-online-schema-change —user=root —password=root —host=172.23.0.2 —alter-foreign-keys-method=auto —alter «MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL» D=DB1,t=product_details —execute .... .... 2019—05—28T09:19:16 Dropping new table... 2019—05—28T09:19:16 Dropped new table OK. `DB255525`.`product_details` was not altered. Error altering new table `DB1`.`_product_details_new`: DBD::mysql::db do failed: Cannot change column ‘product_catalog_id’: used in a foreign key constraint ‘_product_details_ibfk_1’ [for Statement «ALTER TABLE `DB255525`.`_product_details_new` MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL»] at /usr/bin/pt—online—schema—change line 9271. |
It failed while renaming the table _product_details_new to product_details. The reason is that it would have BIGINT datatype in child table and INT datatype in the parent table if the rename succeeds – which is not allowed in MySQL. Foreign keys between different data types are not allowed.
alter-foreign-keys-method=rebuild_constraints
|
# pt-online-schema-change —user=root —password=root —host=172.23.0.2 —alter-foreign-keys-method=rebuild_constraints —alter «MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL» D=DB1,t=product_details —execute ..... ..... Created new table DB1._product_details_new OK. Altering new table... 2019—05—28T09:27:24 Dropping new table... 2019—05—28T09:27:25 Dropped new table OK. `DB255525`.`product_details` was not altered. Error altering new table `DB1`.`_product_details_new`: DBD::mysql::db do failed: Cannot change column ‘product_catalog_id’: used in a foreign key constraint ‘_product_details_ibfk_1’ [for Statement «ALTER TABLE `DB255525`.`_product_details_new` MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL»] at /usr/bin/pt—online—schema—change line 9271. |
In this case, the child table is rebuilt to point to the correct parent table using an ALTER, but it failed again for the same reason. So, none of the options like direct ALTER nor pt-online-schema-change are working for this particular change.
DDL Queries on Foreign Key Columns Workaround
Even disabling foreign key checks don’t work here, as that trick only works when it comes to data, not the schema changes. This has been reported independently under Percona Server as well as in PXC branches in JIRA. You can see more information about these here and here. There is one easy yet simple workaround that I would suggest. That is to drop the foreign key constraint on the child table, run the DDL on both the child and the parent tables, and finally redefine the foreign key constraint.
As you can see, the integrity constraint is compromised for the duration for this workaround. Be sure to keep the server in read-only mode and not allow any changes to these tables, as it might lead to inconsistent data between the parent and child tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Step #1: mysql> ALTER TABLE product_details DROP FOREIGN KEY product_details_ibfk_1 ; Query OK, 0 rows affected (0.95 sec) Records: 0 Duplicates: 0 Warnings: 0 Step #2: # pt-online-schema-change —user=root —password=XXXXXXX —host=172.23.0.2 —alter «MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL» D=DB1,t=product_details —execute ...... Successfully altered `DB1`.`product_details`. Step #3: # pt-online-schema-change —user=root —password=XXXXXXX —host=172.23.0.2 —alter «MODIFY COLUMN catalog_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT» D=DB1,t=product_catalog —execute ....... Successfully altered `DB1`.`product_catalog`. Step #4: mysql> ALTER TABLE product_details ADD FOREIGN KEY product_details_ibfk_1 (product_catalog_id) REFERENCES `product_catalog`(`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ; Query OK, 0 rows affected (1.55 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Summary:
Foreign keys can only be defined and operated between two identical columns. Due to this constraint, DDL queries on columns involved in foreign keys are still a problem in MySQL/PXC, especially when the tables are huge. This workaround, with little downtime for writes, is the only quick way to get this done without spending time on complex logic building and implementation that involves changes on both the DB and the application.
Photo by Vanessa Bucceri on Unsplash