Welcome Guest, Not a member yet? Register   Sign In
Forge: Add foreignkey on existing table
#1

(This post was last modified: 05-24-2020, 09:31 PM by RedskyThirty.)

I have created a class which extends Migration and have added a method to add foreign key to an existing table.
I don't know why it's not possible for the moment in CI but it works like that.

PHP Code:
<?php namespace App\Database;

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

/**
 * Class AdvancedMigration
 *
 * @package App\Database
 * @author Manuel Piton (Redsky Thirty)
 */
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
#2

I also did not find a way to change the table and add the new foreign keys.

It would be a very useful resource in the maintenance of existing systems, where several changes in the tables can occur during its life cycle.
Reply
#3

You nee to use alter table
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply




Theme © iAndrew 2016 - Forum software by © MyBB