Foreign key ON DELETE RESTRICT no working

Hosting Support
1

Website URL

(http://7klada.epizy.com))

Other Information

Here is code for my tables.

Parent

CREATE TABLE Game (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
game_name VARCHAR(20),
game_start_date DATETIME NOT NULL,
game_end_date DATETIME
);

CREATE TABLE Sport (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
sport_name VARCHAR(20) NOT NULL
);

Child

CREATE TABLE GameDetails (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
id_game INT NOT NULL,
id_sport INT NOT NULL,
id_bet_name INT NOT NULL,
id_bet_value INT NOT NULL,
bvbn INT NOT NULL,
FOREIGN KEY (id_game) REFERENCES Game(id) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (id_sport) REFERENCES Sport(id) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (id_bet_name) REFERENCES BetName(id) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (id_bet_value) REFERENCES BetValue(id) ON UPDATE CASCADE ON DELETE RESTRICT
);

Foreign key in GamedDetails is not working. I can delete sport_name or game_name even it is assign to GameDetails.

I saw this theme about foreign key and solution is install Adminer. Must we install it and how or it is possible solve problem in PhpMyAdmin?

2

I had a quick look at your database, and I see the issue. The Game and GameDetails tables are created with the MyISAM storage engine. But MyISAM does not support foreign keys. If you want to use foreign keys, please change the storage engine of the tables to InnoDB instead.

You can do that in phpMyAdmin by navigating to the table and going into the Operations tab.

phpMyAdmin’s relation viewer has, or at least used to have, some problems with managing relations. But that was some time ago, and phpMyAdmin has been updated since, so I don’t know how relevant that information still is. And if you’re writing the SQL queries yourself, it doesn’t matter anyways.

8 Likes
3

Thanks. I deleted old tables and made new with this code but now I have problem with child table.

Parent table

CREATE TABLE BetValue (
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
bet_value VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE BetName (
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
bet_name VARCHAR (20) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE Game (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
game_name VARCHAR(20),
game_start_date DATETIME NOT NULL,
game_end_date DATETIME
) ENGINE=InnoDB;

CREATE TABLE Sport (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
sport_name VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

Child table

CREATE TABLE GameDetails (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
id_game INT NOT NULL,
id_sport INT NOT NULL,
id_bet_name INT NOT NULL,
id_bet_value INT NOT NULL,
bvbn INT NOT NULL

) ENGINE=InnoDB;

Error message:

Can’t create table epiz_33293177_w599.GameDetails (errno: 150 “Foreign key constraint is incorrectly formed”)

I will add later constraints with ALTER TABLE.

4

I was just able to create the GameDetails table on your database, including the foreign keys, without any problems. I don’t know why it didn’t work for you.

5 Likes
5

OK thanks for help.
Can I know why it did change from InnoDB to MyIsam? I didn’t had to define engine before.

6

If you don’t specify an engine, the server’s default engine is used. Maybe this setting was changed at some point. But if you care about which engine is being used, then it’s good to specify it yourself.

7 Likes
7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.