CodeIgniter Forums

Full Version: Migrations
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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: 53

How 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.