Welcome Guest, Not a member yet? Register   Sign In
Adding foreign key to existing table using Migrations
#1

Hi,

Thanks for the great release of CI 4. Its wonderful. 
I recently started working with CI 4 over CI 3. I love the way migrations are setup. However I feel it lacks some functionality or not sure if I don't understand it.
I am trying to add a few foreign keys to an existing table. But I couldn't find anything in documentation except adding foreign keys while creating a new table. 

Can someone please point me into the right direction.

Thanks
Reply
#2

(This post was last modified: 05-07-2020, 12:29 AM by RedskyThirty.)

Have you found a solution?
I don't know why it's only possible at the creation of the table.

Hey, if you want, I made this and it seems to work perfectly.

PHP Code:
<?php namespace App\Database;

use 
CodeIgniter\Database\Exceptions\DatabaseException;
use 
CodeIgniter\Database\Migration;

/**
 * Class AdvancedMigration
 *
 * @package App\Database
 */
abstract class AdvancedMigration extends Migration
{
    
/**
     * Perform a migration step.
     */
    
abstract public function up();
    
    
/**
     * Perform a migration step.
     */
    
abstract public function down();
    
    
    
/**
     * @param string $tableName
     * @param string $fieldName
     * @param string $relatedTableName
     * @param string $relatedTableField
     * @param string $onUpdate
     * @param string $onDelete
     * @param string $indexMethod
     * @return bool
     */
    
protected function addNewForeignKey(
        
string $tableName '',
        
string $fieldName '',
        
string $relatedTableName '',
        
string $relatedTableField '',
        
string $onUpdate '',
        
string $onDelete '',
        
string $indexMethod 'btree'
    
): bool
    
{
        if (!
$this->db->simpleQuery('ALTER TABLE `'.$tableName.'` ADD CONSTRAINT `'.$tableName.'_'.$fieldName.'_foreign` FOREIGN KEY (`'.$fieldName.'`) REFERENCES `'.$relatedTableName.'` (`'.$relatedTableField.'`) ON DELETE '.strtoupper($onDelete).' ON UPDATE '.strtoupper($onUpdate).';'))
        {
            
$error $this->db->error();
            
            throw new 
DatabaseException('An error occured in "AdvancedMigration" (1).\nCode: '.$error['code'].'\nMessage: '.$error['message']);
        }
        
        if (empty(
$error))
        {
            if (!
$this->db->simpleQuery('ALTER TABLE `'.$tableName.'` ADD INDEX `'.$tableName.'_'.$fieldName.'_foreign`(`'.$fieldName.'`) USING '.strtoupper($indexMethod).';'))
            {
                
$error $this->db->error();
                
                throw new 
DatabaseException('An error occured in "AdvancedMigration" (2).\nCode: '.$error['code'].'\nMessage: '.$error['message']);
            }
        }
        
        return 
true;
    }

Reply
#3

(05-06-2020, 11:55 PM)RedskyThirty Wrote: Have you found a solution?
I don't know why it's only possible at the creation of the table.

No I haven't found anything for existing tables yet. I am hoping to get a response from someone responsible and guide me.
Reply
#4

(05-07-2020, 12:22 AM)wntechs Wrote:
(05-06-2020, 11:55 PM)RedskyThirty Wrote: Have you found a solution?
I don't know why it's only possible at the creation of the table.

No I haven't found anything for existing tables yet. I am hoping to get a response from someone responsible and guide me.

Take a look at the edit of my first reply ;-)
Reply
#5

(05-07-2020, 12:58 AM)RedskyThirty Wrote:
(05-07-2020, 12:22 AM)wntechs Wrote:
(05-06-2020, 11:55 PM)RedskyThirty Wrote: Have you found a solution?
I don't know why it's only possible at the creation of the table.

No I haven't found anything for existing tables yet. I am hoping to get a response from someone responsible and guide me.

Take a look at the edit of my first reply ;-)

Thanks! It works perfectly.
Reply
#6
Wink 
(This post was last modified: 12-30-2020, 09:38 PM by dennysw.)

PHP Code:
<?php namespace App\Database\Migrations;

use 
CodeIgniter\Database\Migration;

class 
AddIduserProduk extends Migration
{
    public function 
up()
    {
        
$fields = [
            
'id_user' => [
                
'type'      => 'INT',
                
'constraint'=> 11,
                
'unsigned'  => TRUE,
                
'after'         => 'id_kategori',
                
'null'            => TRUE
            
],
            
'CONSTRAINT produk_ibfk_1 FOREIGN KEY(`id_user`) REFERENCES `users`(`id_user`)'
        
];
        
$this->forge->addColumn('product_table'$fields);
    }

    
//--------------------------------------------------------------------

    
public function down()
    {
        
$this->forge->dropForeignKey('produk','produk_ibfk_1');
        
$this->forge->dropColumn('product_table''id_user');
    }


Its work for me in file app/Database/Migrations/2020-12-31-034310_add_iduser_produk.php
Reply




Theme © iAndrew 2016 - Forum software by © MyBB