Error altering primary key column in mysql table

Question

I am very new to SQL and MySQL. I am trying to modify a primary key column in a table so that it auto-increments. This primary key is also a foreign key in another table. I am not able to modify this column due to an error related to the foreign key in the other table. Here is the error:

mysql> desc favourite_food;
+-----------+----------------------+------+-----+---------+-------+
| Field     | Type                 | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO   | PRI | 0       |       |
| food      | varchar(20)          | NO   | PRI |         |       |
+-----------+----------------------+------+-----+---------+-------+
2 rows in set (0.09 sec)

mysql> alter table person modify person_id smallint unsigned auto_increment;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    22
Current database: bank

ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key cons
traint 'fk_fav_food_person_id' of table 'bank.favourite_food'
mysql>

I'm sure it is something simple, but I can't figure out why and teh book I am following does not indicate why. Thanks.

Why not just drop the fk, do your alter statement then recreate the fk — Java Devil, Jun 24 '13 at 00:14
Would you be able to show me how to do that? Secondly, why does the fk need to be dropped? — fdama, Jun 24 '13 at 00:19

score 4 · Answer 1 · answered Jun 24 '13 at 00:43

4

Do it something like this

--Drop fk
ALTER TABLE favourite_food DROP FOREIGN KEY fk_fav_food_person_id;
--Alter your pk
ALTER TABLE person modify person_id smallint unsigned auto_increment;
--Recreate fk
ALTER TABLE favourite_food ADD CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id) ON DELETE CASCADE;

Haven't checked syntax exactly, but should be close

answered Jun 24 '13 at 00:43

Java Devil

10,629
7
33
48

This is exactly what you need to do. The error told you exactly what was wrong: you have a foreign key constraint that prevents you from modifying the column. Remove that constraint, make your change and then add the constraint back in. – Volte Mar 20 '14 at 18:34
lock the table before doing this, just to avoid problems with data integrity – jairhumberto Jun 01 '15 at 15:49

score 1 · Answer 2 · edited May 23 '17 at 12:20

1

Execute your SQL again and then run

show engine innodb status

Type the above command onto your MySQL command prompt. It should help you with more info on why the SQL failed to execute.

Try this otherwise:

show innodb status

Take a look here: Error code 1005, SQL state HY000: Can't create table errno: 150

edited May 23 '17 at 12:20

Community

1
1

answered Jun 24 '13 at 00:20

sethu

8,181
7
39
65

Juned Ahsan · Answer 3 · 2013-06-24T00:49:31.140

You cannot alter a primary key as it is referenced as a foreign key in other table. This is because of the referential integrity constraint.

Referential Integrity Although the main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table.The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the foreign key constraint of another table. To successfully change or delete a row in a foreign key constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.

Cascading Referential Integrity

By using cascading referential integrity constraints, you can define the actions that the Database Engine takes when a user tries to delete or update a key to which existing foreign keys point. The following cascading actions can be defined.

NO ACTION

The Database Engine raises an error and the delete or update action on the row in the parent table is rolled back.

CASCADE

Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table. CASCADE cannot be specified if a timestamp column is part of either the foreign key or the referenced key. ON DELETE CASCADE cannot be specified for a table that has an INSTEAD OF DELETE trigger. ON UPDATE CASCADE cannot be specified for tables that have INSTEAD OF UPDATE triggers.

SET NULL

All the values that make up the foreign key are set to NULL when the corresponding row in the parent table is updated or deleted. For this constraint to execute, the foreign key columns must be nullable. Cannot be specified for tables that have INSTEAD OF UPDATE triggers.

SET DEFAULT

All the values that make up the foreign key are set to their default values if the corresponding row in the parent table is updated or deleted. For this constraint to execute, all foreign key columns must have default definitions. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. Cannot be specified for tables that have INSTEAD OF UPDATE triggers.

CASCADE, SET NULL, SET DEFAULT and NO ACTION can be combined on tables that have referential relationships with each other. If the Database Engine encounters NO ACTION, it stops and rolls back related CASCADE, SET NULL and SET DEFAULT actions. When a DELETE statement causes a combination of CASCADE, SET NULL, SET DEFAULT and NO ACTION actions, all the CASCADE, SET NULL and SET DEFAULT actions are applied before the Database Engine checks for any NO ACTION.

Error altering primary key column in mysql table

3 Answers3

Linked