Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement