Menu
I have primary key column, which has some external foreign key references. Very usual. I forgot to add AUTO_INCREMENT for it. Now I execute
ALTER TABLE chat.users
CHANGE COLUMN user_id user_id INT(11) NOT NULL AUTO_INCREMENT ;
(PRIMARY KEY was defined separately)
it tells something about fk
ERROR 1833: Cannot change column 'user_id': used in a foreign key constraint 'fk_chats_users' of table 'chat.chats'
I can't figure out why fk bother something about it's reference AUTO_INCREMENT.
341
You can add auto_increment to a column in MySQL database with the help of ALTER command.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
A foreign key is a link to a specific record in another table (or another record in the same table). Creating a foreign key field that is auto-incrementing would create a link to an arbitrary (and possibly non-existent) record, which would defeat the whole purpose of having a foreign key in the first place.
The reason the FK bothers about your changes is because you are trying to alter it and is used in a constraint, remember that you are able to alter the data type.
So if you want to make the change to the FK, check this answer (remember to lock the tables before if you are making the change in a production environment).
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
