Renaming Foreign-Key Columns in MySQL

Renaming foreign-key columns in MySQL

AFAIK, dropping the constraint, then rename, then add the constraint back is the only way. Backup first!

How to rename a field used as foreignkey in mysql?

You could execute the following code, and make the necessary changes according to your column and table names.

ALTER TABLE table_name
DROP FOREIGN KEY fk_constraint_name;

ALTER TABLE table_name
CHANGE fk_column_name new_fk_column_name datatype;

ALTER TABLE table_name
ADD FOREIGN KEY fk_constraint_name
REFERENCES parent_table_name(pk_column_name_id);

Let me know how this works for you.

How do I rename a foreign key in mysql?

From the documentation:

Multiple ADD, ALTER, DROP, and CHANGE clauses are permitted in a
single ALTER TABLE statement, separated by commas. This is a MySQL
extension to standard SQL, which permits only one of each clause per
ALTER TABLE statement.

This way you can combine the drop and recreate into one query, and that should be faster than dropping the constraint and creating it in two queries:

ALTER TABLE conversation_tags
DROP FOREIGN KEY `conversation_tags_ibfk_1`,
ADD CONSTRAINT `fk_conversation_tags_tags` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`);

How to rename foreign key constraint | MySql 5.6, InnoDB

It will not lead to issues.

Adding an FK should discover that there is already a sufficient index and not add a second one. Even if it adds a redundant index, little harm is done.

I wonder -- Why do you care what the constraint names are?

From 5.6 doc: "For ALTER TABLE, unlike CREATE TABLE, ADD FOREIGN KEY ignores index_name if given and uses an automatically generated foreign key name. As a workaround, include the CONSTRAINT clause to specify the foreign key name: ADD CONSTRAINT name FOREIGN KEY (....) .." and "Adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE ... ALGORITHM=INPLACE but not for ALTER TABLE ... ALGORITHM=COPY."

My point in bringing that up is that ALTER usually runs faster if you throw all the changes to a table into a single statement. But DROP + ADD is disallowed in the COPY case.

Renaming columns with foreign key constraints in mysql - anyway to make it easier?

You can rename the fields with dependency check with a dbForge Studio for MySQL (get Cristmas discounts - 20% off, or try Express version ;-). I cannot say the it will be faster, but it will help you to rename these fields and recreate keys automatically in a few steps - just change its name in a Database Explorer and click on Refactor button in message box.

Why does Mysql rename my foreign key?

You must use the CONSTRAINT keyword to name a constraint:

mysql> ALTER TABLE JOB_LISTENER ADD CONSTRAINT FK_JOBS FOREIGN KEY (job_id) 
  REFERENCES job(id);

This automatically names the index to the same name, if it needs to create an index.

mysql> SHOW CREATE TABLE JOB_LISTENER\G
*************************** 1. row ***************************
       Table: JOB_LISTENER
Create Table: CREATE TABLE `JOB_LISTENER` (
  `job_id` int(11) DEFAULT NULL,
  KEY `FK_JOBS` (`job_id`),
  CONSTRAINT `FK_JOBS` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html says:

If the CONSTRAINT symbol clause is given, the symbol value, if used, must be unique in the database. A duplicate symbol will result in an error similar to: ERROR 1022 (2300): Can't write; duplicate key in table '#sql- 464_1'. If the clause is not given, or a symbol is not included following the CONSTRAINT keyword, a name for the constraint is created automatically.

Sometimes you have to read between the lines. In the above paragraph, I infer from the last sentence that you must use the CONSTRAINT keyword to give a name for the constraint, because if you don't, then the constraint name is created automatically.

Tip: Be aware that constraint names must be unique within the database, not only within the table. Go figure, this is part of the SQL standard. So you may not expect this, so be careful not to use duplicate constraint names.

Whereas in some implementations you can use the same index name in multiple tables in the same database (the SQL standard does not cover index implementation, as a_horse_with_no_name reminds me).

---

Related Topics