CODEWITHSUNDEEP
mysqlcodeigniter
Ferdinand
Ferdinand

Reputation: 117

Cannot truncate a table referenced in a foreign key constraint in Codeigniter

i'm making a DB seeder using CodeIgniter

here is the code

class AreasSeeder extends Seeder {

private $table = 'areas';

public function run() {
    $this->db->truncate($this->table);

    $data = [
        [
            'name' => 'area1'
        ],
        [
            'name' => 'area2'
        ]
    ];

    $this->db->insert_batch($this->table, $data);

    echo PHP_EOL;
    }
}

when it run this display error

Error Number: 1701
    Cannot truncate a table referenced in a foreign key constraint (`myproject`.`area_pref_relations`, CONSTRAINT `area_pref_relations_ibfk_1` FOREIGN KEY (`area_id`) REFERENCES `myproject`.`areas` (`id`))
    TRUNCATE `areas`
    Filename: database/seeds/areaSeeder.php
    Line Number: 8

do i need to reset all table related on table areas?

Upvotes: 2

Views: 9759

Answers (1)

spencer7593
spencer7593

Reputation: 108420

This isn't specific to CodeIgniter.

It's a MySQL restriction. Use a DELETE statement instead (assuming that no foreign key rows are referencing rows in the table we're deleting rows from, or foreign key constraints are defined ON DELETE CASCADE, or ON DELETE SET NULL.

DELETE FROM `areas`;

If we want to use TRUNCATE, we will need to disable the foreign key constraints. Either drop the key constraint(s) that reference the table, then do the TRUNCATE on the table and recreate the foreign key constraint(s).

ALTER TABLE `myproject`.`area_pref_relations` 
  DROP FOREIGN KEY `area_pref_relations_ibfk_1` ; 

TRUNCATE TABLE `areas` ;

ALTER TABLE `myproject`.`area_pref_relations` 
  ADD CONSTRAINT `area_pref_relations_ibfk_1`
  FOREIGN KEY (`area_id`) REFERENCES `myproject`.`areas` (`id`))
  ON UPDATE CASCADE ON DELETE CASCADE ;

Or, if we know that that there are no foreign key rows referencing rows in the table we're truncating, it might be possible be to disable foreign key checks for the session, do the TRUNCATE, and then re-enable foreign keys. 

 SET foreign_key_checks = 0 ;
 TRUNCATE TABLE my_referenced_table; 
 SET foreign_key_checks = 1 ;

Upvotes: 6

Related Questions