CODEWITHSUNDEEP
mysql
Sinisa P.
Sinisa P.

Reputation: 1132

How change/drop foreign key on MySql table and associate to another table?

tbl_table1 have columns 'Id', 'Type_Id' and 'Name'.
'Type_Id' is Foreign_Key that reference to tbl_table2 'Type_Id'

I have make a copy of tbl_table2 and renamed it to tbl_glob_table2 with same values and columns.

My problem:
How can i change the Foreign_Key reference from tbl_table2.Type_Id to tbl_glob_table2.Type_Id

Error message when i will update tbl_table1:
"SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails.

ALTER TABLE tbl_table1 DROP FOREIGN KEY Type_Id
give me following error message:
#1091 - Can't DROP 'Type_Id'; check that column/key exists

Create Table statement:

SHOW CREATE TABLE tbl_table1
    CREATE TABLE `tbl_table1` (
     `Id` varchar(10) COLLATE utf8_bin NOT NULL,
     `Type_Id` int(3) NOT NULL,
     `Name` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT 'noname',
     PRIMARY KEY (`Id`),
     KEY `FK_tbl_table1_type` (`Type_Id`),
     CONSTRAINT `tbl_table1_ibfk_2` FOREIGN KEY (`Type_Id`) REFERENCES `tbl_table2` (`Type_Id`) ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Upvotes: 0

Views: 84

Answers (1)

Shadow
Shadow

Reputation: 34285

You have to use the symbol name (name of the constraint) to drop a foreign key, not the name of the index. In this case: tbl_table1_ibfk_2.

ALTER TABLE tbl_table1 DROP FOREIGN KEY tbl_table1_ibfk_2

Upvotes: 1

Related Questions