CODEWITHSUNDEEP
phpmysqlyiiforeign-keysdatabase-migration
ZeD
ZeD

Reputation: 374

How to delete a key from table using CDbMigration

This is a structure of my table:

CREATE TABLE `company_info` (
  `id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_polish_ci DEFAULT NULL,
  `description` longtext COLLATE utf8_polish_ci,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_info` date DEFAULT NULL,
 `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Index_1` (`id`),
 KEY `FK_Reference_4` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci

I need to change it, using CDbMigration in Yii 1.x. Currently, key FK_Reference_4 (company_id) is a foreign key to id in table company. I'm trying to:

When im trying to execute migration, using yiic migration, it is failing on command:

$this->dropForeignKey('FK_Reference_4','company_info');

with following error:

Exception: CDbCommand failed to execute the SQL
statement: SQLSTATE[HY000]: General error: 1025 Error on rename of '.\focuserv\
company_info' to '.\focuserv\#sql2-1b0-18' (errno: 152). The SQL statement execu
ted was: ALTER TABLE `company_info` DROP FOREIGN KEY `FK_Reference_4` (E:\IDEs\w
amp\www\focuserv\focuserv\framework\db\CDbCommand.php:358)

So dropForeignKey is failing. How should I fix or workaround this problem?

Upvotes: 2

Views: 3461

Answers (1)

Pitchinnate
Pitchinnate

Reputation: 7556

Based off of your create table sql statement above it doesn't look like your FK_Reference_4 is a foreign key it looks like a normal index. Try changing 

$this->dropForeignKey('FK_Reference_4','company_info');

to 

$this->dropIndex('FK_Reference_4','company_info');

FYI a foreign key sql statement looks like this:

ALTER TABLE `company_info` 
ADD CONSTRAINT `FK_Reference_4` FOREIGN KEY (`company_id`) 
REFERENCES `company` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Upvotes: 2

Related Questions