Foreign key NOT working

[phppup]

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.

[Barand]

Show us the CREATE TABLE code for those two tbles.

[phppup]

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

 

[benanamen]

What are you trying to accomplish? What you have will delete all exams for a student when you delete a particular student

Edited June 1, 2022 by benanamen

[phppup]

@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.

[benanamen]

Your schema worked just fine for me. Are you sure you didn't have spaces at the start or end of your data?

[Barand]

Your create table doesn't show whether they are InnoDb or not. They need to be, MyISAM won't work.

[phppup]

@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

 

[gizmola]

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...).

[phppup]

 

@gizmola  Thanks for the helpful information.