+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Migrations (/showthread.php?tid=66101)
Migrations - mgilberties - 09-05-2016
I am using migrations to create some tables, something which is new to me.
I have the following tables:
Customer
id - PK
company_name
type_id - FK - Customer_Type(id)
Customer_Type
id - PK
type_description
My migration looks like so:
PHP Code:
<?php
class Migration_Customer extends CI_Migration {
public function up() {
$this->dbforge->add_field(array(
'id' => array(
'type' => 'INT',
'constraint' => 11,
'auto_increment' => TRUE
),
'company_name' => array(
'type' => 'VARCHAR',
'constraint' => 100
),
'type_id' => array(
'type' => 'INT',
'constraint' => 11
)
));
$this->dbforge->add_key('id', TRUE);
$this->dbforge->add_key('type_id');
$this->dbforge->create_table('customer');
$this->dbforge->add_field('CONSTRAINT FOREIGN KEY (type_id) REFERENCES customer_type (id)');
}
public function down() {
$this->dbforge->drop_table('customer');
}
}
The problem being this migration runs before the next which creates the Customer_Type lookup table and thus fails with:
Code:
Database error: A Database Error Occurred
Error Number: 1215
Cannot add foreign key constraint
CREATE TABLE `customer` (
CONSTRAINT FOREIGN KEY (type_id) REFERENCES customer_type (id),
`id` INT(11) NOT NULL AUTO_INCREMENT,
`company_name` VARCHAR(30) NOT NULL,
`type_id` INT(11) NOT NULL,
CONSTRAINT `pk_customer` PRIMARY KEY(`id`),
KEY `type_id` (`type_id`)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci
Filename: database/migrations/20160904162852_Customer.php
Line Number: 53How do people handle the addition of constraints/foreign keys through migrations? Am I to lump related tables into one migration and then add the constraint after the creation of each table?
Open to suggestions and best practices.