FK Maker

FK Maker

<?php
$dsn = 'mysql:host=localhost;dbname=information_schema';
$username = 'root';
$password = 'password';
$dbh = new PDO($dsn, $username, $password);

$tableSchema  = 'db_name';
$fkColumnName = 'emp_id';
$pkColumnName = 'admin_id';
$pkTableName  = 'admin_login';
$fkDataType   = 'BIGINT';
$fkDataSize   = 20;


if (!is_dir('sqls')) {
    mkdir('sqls', 0755);
}

$file    = "sqls/{$fkColumnName}.sql";
$handle  = fopen($file, 'w');
$write   = '';

// Fetch all tables which having `$fkColumnName` foreign key
$sql    = "Select * From INFORMATION_SCHEMA.COLUMNS WHERE column_name = '{$fkColumnName}' AND table_schema = '{$tableSchema}'";
$stmt   = $dbh->query($sql, PDO::FETCH_ASSOC);
$tables = $stmt->fetchAll();

foreach ($tables as $key => $table)
{
    $tableName = $table['TABLE_NAME'];

    if ($tableName == $pkTableName) {
        continue;
    }

    // Check for index exists on foreign key
    $sql = "SELECT * FROM information_schema.statistics  WHERE table_schema = '{$tableSchema}' AND table_name = '{$tableName}' AND column_name = '{$fkColumnName}';";

    $stmt           = $dbh->query($sql, PDO::FETCH_ASSOC);
    $is_index_exist = $stmt->fetchAll();

    if (count($is_index_exist) == 0) {
        $write .= "\nALTER TABLE `{$tableName}` ADD INDEX(`{$fkColumnName}`);";
    }

    // Delete all unrelated record
    $write .= "\nDELETE FROM `{$tableName}` WHERE `{$fkColumnName}` NOT IN (select {$pkColumnName} from `{$pkTableName}`);";

    $sql = "\n
    SELECT
        TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
        REFERENCED_TABLE_SCHEMA = '{$tableSchema}' AND
        REFERENCED_COLUMN_NAME = '{$pkColumnName}' AND
        TABLE_NAME = '{$tableName}' AND
        REFERENCED_TABLE_NAME = '{$pkTableName}';";

    $stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
    $record = $stmt->fetchAll();

    $fkName = false;
    if ($record)
    {
        // drop FK
        foreach($record as $row) {
            $tableName  = $row['TABLE_NAME'];
            $fkName     = $row['CONSTRAINT_NAME'];
            $columnName = $row['COLUMN_NAME'];
            $referTName = $row['REFERENCED_TABLE_NAME'];
            $referCName = $row['REFERENCED_COLUMN_NAME'];
            $write .= "\nALTER TABLE $tableName DROP FOREIGN KEY `$fkName`;";
        }
    }

    // add FK
    $write .= "\nALTER TABLE `{$tableName}` CHANGE `{$fkColumnName}` `{$fkColumnName}` {$fkDataType}({$fkDataSize}) NOT NULL;";
    //$write .= "\nALTER TABLE `$tableName` CHANGE `admin_id` `admin_id` INT(11) UNSIGNED NOT NULL;";

    if ($fkName)
    {
        $write .= "\nALTER TABLE `$tableName` ADD CONSTRAINT $fkName FOREIGN KEY (`{$fkColumnName}`) REFERENCES `{$pkTableName}` (`{$pkColumnName}`) ON UPDATE RESTRICT ON DELETE RESTRICT;\n";
    }
    else
    {
        $write .= "\nALTER TABLE `$tableName` ADD FOREIGN KEY (`{$fkColumnName}`) REFERENCES `{$pkTableName}` (`{$pkColumnName}`) ON UPDATE RESTRICT ON DELETE RESTRICT;\n";
    }

    $write .= "\n\n";
}

$stmt->closeCursor();
echo $write;
file_put_contents($file, $write);