deleting table with a foreign key

Question

I am trying to delete a column with a foreign key in it. I am sitting on this for a while know and it feels like I am overseeing something and the awnser must be obvious, but I just dont get it. The error I get from the loggs is:

PHP Fatal error: Cannot declare class User, because the name is already in use user_id is a foreign key of the Identifier from the Class user.

The database I use is InnoDB. I have tried this:

  $drop = 'ALTER TABLE `Group` DROP FOREIGN KEY `user_id`';
    $stmt = $conn->prepare($drop);
    $stmt->execute();

However this had no effect, no error in the loggs or anything.

I apologize if this is obvious, Im a student and this is my 3rd week. ¯_(ツ)_/¯

<?php
      require 'connect.php';
      require 'model.php';
      $identifier = null;

      if(!empty($_GET['identifier'])) {
        $identifier = $_REQUEST['identifier'];
      }


      if(!empty($_POST)) {
        require 'connect.php';
        require 'model.php';


        $identifier = $_REQUEST['identifier'];
        settype($identifier, 'integer');
        $group = Group::retrieve($conn, $_GET['identifier']);


        Group::delete($conn, $group);
        header('Location: view.php');
      } else {
        echo 'Its not working';
      }

    ?>

<!DOCTYPE html>
<html lang='en'>
<head>
  <meta charset='utf-8'>
  <link href='bootstrap/bootstrap/css/bootstrap.min.css' rel='stylesheet'>
  <script src='bootstrap/bootstrap/js/bootstrap.min.js'></script>
</head>

<body>
  <div class='container'>

    <div class='span10 offset1'>
      <div class='row'>
        <h3>Delete</h3>
      </div>

      <form class='form-horizontal' action='delete.php?identifier=<?php echo $identifier?>' method='post'>
        <input type='hidden' name='identifier' value='<?php echo $identifier;?>'/>
        <p class='alert alert-error'>DO YOU REALLY WANT TO Delete?</p>
        <div class='form-actions'>
          <button type='submit' class='btn btn-danger'>EXTERMINATE</button>
          <a class='btn' href='view.php'>No</a>
        </div>
      </form>
    </div>

  </div>
</body>
</html>

The relevant parts of the Class Group in my model looks like this:

 public static function retrieve($conn, $identifier) {
    $query = 'SELECT * FROM `Group` WHERE `identifier` = :identifier';
    $stmt = $conn->prepare($query);
    $stmt->bindParam(':identifier', $identifier, PDO::PARAM_INT);
    $stmt->execute();

    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    return new Group((int)$result['identifier'], $result['admin'], 
   $result['superuser'], $result['testaccount'], (int)$result['user_id']);
   }

  public static function delete($conn, Group $group) {
    $query = 'DELETE FROM `Group` WHERE identifier = :identifier, admin = :admin,
    superuser = :superuser, testaccount = :testaccount, user_id = :user_id';

    $stmt = $conn->prepare($query);

    $stmt->bindValue(':identifier', $group->getIdentifier(), PDO::PARAM_INT);
    $stmt->bindValue(':admin', $group->getAdmin(), PDO::PARAM_STR);
    $stmt->bindValue(':superuser', $group->getSuperuser(), PDO::PARAM_STR);
    $stmt->bindValue(':testaccount', $group->getTestaccount(), PDO::PARAM_STR);
    $stmt->bindValue(':user_id', $group->getUser_id(), PDO::PARAM_INT);

    $stmt->execute();

    return TRUE;

  }

Regarding queries one thing you can try first is to execute it on MySQL console(phpmyadmin or mysql workbench etc) first to see whether it works or not. if it works then problem is with the php code. otherwise your query is incorrect. This helps to save a lot of time. — Krishnadas PC, Sep 10 '18 at 07:11
Thanks! That helped a lot! My boss actually mentioned that already, but I was so overwhelmed with new information that i forgot. — Yranna, Sep 10 '18 at 07:43

score 0 · Answer 1 · answered Sep 10 '18 at 07:18

Check this if your query failed in the MySql console. if it failed then it means constraint name can be incorrect. If that is the case try the below code. Foreign key has to be removed with it's constraint name not by column name.

To get the constraint name you can use

SHOW CREATE TABLE Group

ALTER TABLE `Group` DROP FOREIGN KEY `your_constraint_name_here`

More info can be found at

MySQL Removing Some Foreign keys

score 0 · Accepted Answer · answered Sep 10 '18 at 07:46

Thank you for your help. I had an error in my code to drop the foreign key, it worked with this:

 $drop = 'ALTER TABLE `Group` DROP FOREIGN KEY `Group_ibfk_1`';
 $stmt = $conn->prepare($drop);
 $stmt->execute();

Also in the delete function it needed to be AND instead of a commata, like this:

$query = 'DELETE FROM `Group` WHERE identifier = :identifier AND admin = :admin AND
    superuser = :superuser AND testaccount = :testaccount AND user_id = :user_id';

Anyway is this considered good practice? Is there an other way to deal with deleting columns with foreign keys, other then dropping the key for awhile?

since you are new here, kindly mark the correct answer as accepted so that it will be closed. I recommend you to read this page https://stackoverflow.com/tour to get the best out of Stackoverflow. — Krishnadas PC, Sep 10 '18 at 08:41

deleting table with a foreign key

2 Answers2