Welcome Guest, Not a member yet? Register   Sign In
How to add column with foreign key within migration?
#1

I would like to add new column in database within migration, but I want to add also foreign key. 
I already have a table devices and I want to add column customer_id with relation to customers table.

Here is a migration

PHP Code:
<?php

declare(strict_types=1);

namespace 
Modules\Customers\Database\Migrations;

use 
CodeIgniter\Database\Migration;

class 
AlterDevices extends Migration
{
    public function up(): void
    
{
        $fields = [
            'customer_id' => [
                'type'    => 'int',
                'unsigned' => true,
                'after'    => 'id',
            ],
        ];

        $this->forge->addColumn('devices'$fields);
        $this->forge->addForeignKey('customer_id''customers''id');
    }

    public function down(): void
    
{
        $this->forge->dropColumn('devices''customer_id');
    }



But when I ran php spark migrate -all I'm getting

Code:
Field `customer_id` not found.


and migration fail.
Reply
#2

Please Read:

CodeIgniter 4 User Guide - Foreign Keys

CodeIgniter 4 User Guide - Adding Foreign Keys
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

@InsiteFX  thanks, but I can't find what I needed in docs. 

Here is the working example how to get it to work. I ended with alter query. 

PHP Code:
<?php

declare(strict_types=1);

namespace 
Modules\Customers\Database\Migrations;

use 
CodeIgniter\Database\Migration;

class 
AlterDevices extends Migration
{
    /**
    * @psalm-suppress UndefinedInterfaceMethod
    */
    public function up(): void
    
{
        $prefix $this->db->getPrefix();

        $fields = [
            'customer_id' => [
                'type' => 'int',
                'unsigned' => true,
                'after' => 'id',
            ],
        ];

        $this->forge->addColumn('devices'$fields);

        $alterQuery = <<<CODE_SAMPLE
                ALTER TABLE {$prefix}devices
                ADD FOREIGN KEY 
{$prefix}devices_customer_id_foreign(customer_id) REFERENCES {$prefix}customers(id) ON DELETE CASCADE;
            CODE_SAMPLE;
        
$this->db->query($alterQuery);
    }

    public function down(): void
    {
        
$this->forge->dropForeignKey('devices', 'devices_customer_id_foreign');
        
$this->forge->dropColumn('devices', 'customer_id');
    }

Reply
#4

Yes, you need to use query() method now.

The feature to add keys to existing tables will be implemented in v4.3.0.
See https://github.com/codeigniter4/CodeIgni....rst#forge

PHP Code:
$this->forge->addKey(['category''name'], falsefalse'category_name');
$this->forge->addPrimaryKey('id''pk_actions');
$this->forge->addForeignKey('userid''user''id''''''userid_fk');
$this->forge->processIndexes('actions');
/* gives:
ALTER TABLE `actions` ADD KEY `category_name` (`category`, `name`);
ALTER TABLE `actions` ADD CONSTRAINT `pk_actions` PRIMARY KEY(`id`);
ALTER TABLE `actions` ADD CONSTRAINT `userid_fk` FOREIGN KEY (`userid`) REFERENCES `user`(`id`);
*/ 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB