Jump to content

Foreign key NOT working


phppup
 Share

Go to solution Solved by Barand,

Recommended Posts

I felt fairly confident of my understanding and code for a second table with a foreign key, yet in testing, when I deleted a record in Table1, it did NOT get deleted in Table2 (which holds the foreign key that references Table1)

I even went so far as to replicate the steps from https://www.geeksforgeeks.org/mysql-deleting-rows-when-there-is-a-foreign-key/   

but the final steps for After Deleting did not match up ( as I continued to have 4 records  in the subordinate table) because the record that should have been removed was still there.

I have tried to troubleshoot and ran 

SHOW GLOBAL VARIABLES LIKE 'foreign_key_checks';

directly in MySQL at phpMyAdmin and received


Variable_name            Value
foreign_key_checks        ON

as my result.

Guidance, please.

Link to comment
Share on other sites

CREATE TABLE student_details
(
     student_id INT PRIMARY KEY,
     Student_name varchar(8),
     student_year varchar(8)
);


CREATE TABLE student_exam
(
  exam_id INT PRIMARY KEY,
  exam_name varchar(8),
  student_id INT,
  FOREIGN KEY(student_id) 
  REFERENCES student_details(student_id) 
  ON DELETE CASCADE
);

 

Link to comment
Share on other sites

@benanamen

Quote

What you have will delete all exams for a student when you delete a particular student

That's what I thought.  But if you READ my opening entry, you'll understand that when I deleted a particular student, the particular student was deleted from the PRIMARY table ONLY.

The child table did NOT get affected at all.

And that's a problem.

Link to comment
Share on other sites

4 minutes ago, phppup said:

@Barand

Ran a new test with ENGINE=INNODB; and had SUCCESS.    Thank you.

Oddly, after ALTERing the table to INNODB, the problem persisted.

But recreating the tables worked

 

 

All the tables involved must be InnoDB.  InnoDB is now the default engine for MySQL, although in the past myisam was the default engine.  Make sure that all your tables are InnoDB, regardless of whether or not they are used in relations.  The InnoDB engine has a number of other features that are important (data caching, row level locking, transactions, Clustered Indexes...).

  • Like 1
Link to comment
Share on other sites

  • 2 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.