0

I want to add comments to columns for already created users table in MySQL database

I am trying following command

ALTER TABLE users CHANGE id id INT(11) NOT NULL COMMENT 'id of user';

But I am getting following error as the id column is used as foreign key in user_address table

SQL Error [1833] [HY000]: Cannot change column 'id': used in a foreign key constraint 'user_address_users_FK' of table 'user_address'

How to do add comment to id column without getting this error? Also is there a way to add comment without a need to mention column definition again?

harv3
  • 263
  • 1
  • 6
  • 19
  • Does this answer your question? [Alter MySQL table to add comments on columns](https://stackoverflow.com/questions/2162420/alter-mysql-table-to-add-comments-on-columns) – Nicola Lepetit Jan 27 '20 at 10:16
  • Post truncated (remove unrelated fields/indices) DDL of both tables. – Akina Jan 27 '20 at 10:19

1 Answers1

1

The id column of the users table is a AUTO_INREMENT column right?

If you don't specify the AUTO_INCREMENT on the ALTER TABLE, MySQL removes the AUTO_INCREMENT on the column id. So you have to add the AUTO_INCREMENT to the ALTER TABLE command too:

ALTER TABLE users MODIFY id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id of user';

demo on dbfiddle.uk

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87