• 0

MySql foreign keys not respected


Question

I'm using MySql 5.0a and I've also tried it on 4.1a but am unable to get this to work: I've set the foreign key constraints on my table to be null when deleted but it doesn't work!

CREATE TABLE Friend (

sid INTEGER,

name CHAR(20),

friend_sid INTEGER,

PRIMARY KEY (sid),

FOREIGN KEY (friend_sid) REFERENCES Friend (sid) ON DELETE SET NULL

);

Then:

INSERT INTO Friend VALUES (1,'Bob',2);

INSERT INTO Friend VALUES (2,'John',3);

Then:

DELETE FROM Friend WHERE name='John';

It should then set friend_sid of Bob to NULL. But it doesn't!

Can anyone help?

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

I found out the reason to be what I think is because the tables are not of the InnoDB type (by using SHOW TABLE STATUS;). But the docs say that InnoDB should be turned on my default. I tried setting them manually with CREATE TABLE Friend (..) TYPE=InnoDB; but it fails the command.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.