+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: How to add column with foreign key within migration? (/showthread.php?tid=84719)
How to add column with foreign key within migration? - jozefrebjak - 11-03-2022
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.
RE: How to add column with foreign key within migration? - InsiteFX - 11-04-2022
Please Read:
CodeIgniter 4 User Guide - Foreign Keys
CodeIgniter 4 User Guide - Adding Foreign Keys
RE: How to add column with foreign key within migration? - jozefrebjak - 11-20-2022
@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');
}
}
RE: How to add column with foreign key within migration? - kenjis - 11-20-2022
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/CodeIgniter4/blob/4.3/user_guide_src/source/changelogs/v4.3.0.rst#forge
PHP Code:
$this->forge->addKey(['category', 'name'], false, false, '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`);
*/