Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL drop foreign key constraint only if it exists

Been looking for almost an hour now, and i can't believe i haven't figured out how to do this yet. I've found this:

Drop constraints only if it exists in mysql server 5.0

but the link offered there is not enough info to get me there.. Can someone offer an example with code, please?

UPDATE

Sorry i wasn't clear in the original question, but i was hoping for a way to do this in just SQL, not utilizing any application programming.

like image 606
ilasno Avatar asked Nov 07 '13 00:11

ilasno


People also ask

How remove foreign key exists in MySQL?

If you want to drop foreign key if it exists and do not want to use procedures you can do it this way (for MySQL) : set @var=if((SELECT true FROM information_schema.

How do I delete a foreign key constraint in SQL?

To delete a foreign key constraint In Object Explorer, expand the table with the constraint and then expand Keys. Right-click the constraint and then click Delete.


1 Answers

example php code:

function removeFK(PDO $pdo, $dbName, fkName)
{
    echo "Removing foreign key '$fkName' from database: $dbName\t";

    $exists = $pdo->query("
        SELECT TRUE
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
            AND TABLE_SCHEMA = '$dbName'
            AND CONSTRAINT_NAME = '$fkName'
        ")->fetchColumn();

    if ($exists === false) {
        echo " [SKIPPING] (FK does not exist)\n";
        return false;
    }

    $pdo->query("USE $dbName");
    $pdo->query("
        ALTER TABLE intelligence_webpage_has_region_keyword
        DROP FOREIGN KEY $fkName");

    echo "[OK]\n";
    return true;
}
like image 160
Ikar Pohorský Avatar answered Oct 23 '22 09:10

Ikar Pohorský