$10
How do I change the key referenced by a constraint?

A stupid mistake on my part. I created a new table by copying and pasting an old table. However, I forgot to change the name of the CONSTRAINT, which now refers to the wrong key. This did not generate an error, but it is leading to some bizarre results. Here is the table:

CREATE TABLE cao_old_cds_ids (

  id int(11) NOT NULL AUTO_INCREMENT,

  user_id int(11)  NOT NULL,

  old_cds_id varchar(100) NOT NULL,

  PRIMARY KEY (id),

  KEY cao_old_cds_ids_FI_1 (user_id),

  CONSTRAINT files_FK_1 FOREIGN KEY (user_id) REFERENCES sf_guard_user (id)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

How do I change the CONSTRAINT to reference the key cao_old_cds_ids_FI_1?

I should explain, after I created the table, we ran import scripts that took hours to complete. So, even though I could fix the above by deleting the above table and starting over, I'd rather not. We do not want to have to run all the import scripts again. So I'd rather fix the problem in place.

Lawrence Krubner | 07/13/10 at 6:22pm | Edit

constraint, foreign keys, key

(2) Possible Answers Submitted...

Last edited:
07/14/10
11:12am
Oleg Butuzov says:
not sure i have understand your question...
but isn't its possible with
ALTER TABLE + DROP INDEX + ADD INDEX ?

also is CONSTRAINT means that table (actualy both tables) should be innoDB?

Previous versions of this answer: 07/13/10 at 6:31pm
- 07/14/10 10:42am
  
  Lawrence Krubner says:
  Hmm, good catch about MyISAM versus innoDB. Maybe I should scrap this table and start over.
Last edited:
07/13/10
6:46pm
Jarret Minkler says:

Aren't the FK_1 and the constraint both referencing user_id? There should be no reason to drop the constraint you have in place.

The "odd results" are probably as Olag mentions - that they should be innoDB. innoDB is the engine with foreign keys.
- 07/14/10 10:41am
  
  Lawrence Krubner says:
  Well, the key is "cao_old_cds_ids_FI_1" but the constraint is referencing "files_FK_1", which was defined on another table.