SQL Autoincrement on Foreign key (MySQL)

Question

I have a table called "Address" it reference to the table "Member", Address has a column called "id" , this column is a foreignkey. I forgot to add a autoincroment on this column.

Member:

• id
• name
• lastname
• ....
• address_id (foreignkey)

Address:

• id (this should be autoincreoment)
• street
• number
• place
• zipcode

when i try in sql

ALTER TABLE Address MODIFY COLUMN id INT auto_increment;

it throughs an error:

Error Code: 1833. Cannot change column 'id': used in a foreign key constraint 'fk_Member_Address1' of table 'mydb.member'

Answer 1

Try to fire these queries in sequence:

1. ALTER TABLE Member DROP FOREIGN KEY address_id;

2. ALTER TABLE Address MODIFY COLUMN id INT auto_increment;

3. ALTER TABLE Member ADD FOREIGN KEY (address_id) REFERENCES Address(id);

Here you first remove foreign key constraint from member table, then add auto_increment to address table and then again add foreign key constraint to member table.

Hope it helps.

Answer 2

Firstly, you have to drop the foreign key constraint Then change the name and recreate the constraint.