Foregin Key Cascade Problem - Please Help

Hello Everybody,

I am new to this forum & This is my first post. I don't know how I can post better so that you can understand this problem. I am taking an simple example.

suppose, I am making ab Database in MySql & it has two tables such as "parent" & "child" & I am making these two tables by using queries as follows.

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

Code (markup):

by using these queries, i am expecting that when I will delete any record from "parent" table, then MySql engine will delete the records from the "child" table automatically corresponding to the foreign key records. Here is an example, suppose I have these following values in "parent" table:

id
1
2
3

& following valves in "child" table

id parent_id
1 2
2 2
3 1
4 3

Now, When I will delete "2" from "parent" table, then MySql Engine have to delete 1st two records from "child" table. But its not happening

Another problem, When I will try to add an record in "child" table such as

insert into child(id, parent_id) values(5, 4);

Code (markup):

then it should have to show an error like:

Cannot add or update a child row: a foreign key constraint fails because "4" is not present in "parent" table.

but I am able to add such records. Can someone please help me that how I can make these things possible that when I will delete any record from parent table then records from child table deleted by MySql Engine & if I will try to add that record into child table which is not present in parent table, then it shows error message.

Thanks in advance,
Jimmy

 

Hello, Jimmy,

It seems that your sql server doesn't support InnoDB.
Run query:

SHOW ENGINES;

Code (markup):

If InnoDB is enabled you should see:

Otherwise, if Support = "NO" you can google to see how to enable it.
Regards,
Nick

 

Hello Koko5,

Thanks for replying for my post. I checked in my phpAdmin by running this query

SHOW ENGINES;

Code (markup):

& it shows following results:

Engine: InnoDB
Support: NO

It means I need to active this engine if I want desired results.

Thanks for your help, You are the man.....

Jimmy