0

When I run this query :

ALTER TABLE `users` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=56;

It gives me error like :

#1833 - Cannot change column 'id': used in a foreign key constraint 'designation_user_user_id_foreign' of table 'databasename.designation_user'

Nage
  • 23
  • 1
  • 5
  • what type is users.id to start with? are you trying to change the type or just set auto_increment? it doesn't make much sense to have auto_increment on a foreign key constraint; presumably you should always be setting a value that is in the other table, or if not you shouldn't have the constraint – ysth Oct 14 '22 at 07:17
  • What are you actually trying to achieve here, what is the goal or outcome you’re wanting? Are you trying to fill a gap in the ID column left by deleting a record? – Peppermintology Oct 14 '22 at 07:24
  • 1
    I suggest you back up production before you do anything. Also test your hypothesis by recovering to test environment. – P.Salmon Oct 14 '22 at 07:34
  • *Is it safe ??* You have not provided enough info - you'd provide CREATE TABLE for both tables. Formally this is NOT safe. Safe method is: #1 - drop FK; #2 - modify column; #3 - recreate FK. – Akina Oct 14 '22 at 07:37
  • Is it safe? not sure. Make a backup. Side note: you should use migrations instead of importing directly in the database in the future. – Gert B. Oct 14 '22 at 08:47

1 Answers1

0

Found a solution that worked! I made id column in users table unique using more option in action column of users table and then executed below query :

ALTER TABLE `users` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
Nage
  • 23
  • 1
  • 5