CODEWITHSUNDEEP
mysqlforeign-keys
ilasno
ilasno

Reputation: 724

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.

Upvotes: 7

Views: 10014

Answers (1)

Ikar Pohorský
Ikar Pohorský

Reputation: 5039

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;
}

Upvotes: 2

Related Questions