2

As I read on https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html, MyISAM storage engine doesn't support foreign key on MySQL 8.0. But when i tried on my database, the foreign key is created successfully.

Can anyone tell me what is the true meaning of MyISAM doesn't support foreign key?

Note, i tried the code on Navicat with MySQL v.8.0

Screen Capture of my Navicat when I created the tables [the result of SHOW CREATE TABLE utama;]1

hamzbond
  • 33
  • 5
  • Can you do show us the output of: `show create table utama;` – Ivan86 Dec 11 '19 at 06:10
  • i have edited the post and add the result of the query – hamzbond Dec 11 '19 at 09:04
  • Your picture of code (and please post code as text, don't take screen-shots of it!) doesn't even attempt to create a foreign key. – Álvaro González Dec 11 '19 at 09:10
  • Here is my code to create the tables. CREATE TABLE utama (id_utama INT NOT NULL PRIMARY KEY AUTO_INCREMENT, nama VARCHAR(12), id_samping INT) ENGINE = MyISAM; CREATE TABLE samping (id_samping INT NOT NULL PRIMARY KEY, posisi VARCHAR(12)) ENGINE = MyISAM; ALTER TABLE utama ADD FOREIGN KEY(id_samping,nama) REFERENCES samping(id_samping, posisi); – hamzbond Dec 11 '19 at 09:32
  • And here is the result of SHOW CREATE TABLE utama. CREATE TABLE `utama` ( `id_utama` int(11) NOT NULL AUTO_INCREMENT, `nama` varchar(12) DEFAULT NULL, `id_samping` int(11) DEFAULT NULL, PRIMARY KEY (`id_utama`), KEY `id_samping` (`id_samping`,`nama`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci – hamzbond Dec 11 '19 at 09:34
  • Use InnoDB instead of MyISAM. – Rick James Dec 12 '19 at 05:33

1 Answers1

1

For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

from the docs

That means you can define a foreign key but it does not have any effect.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • i haven't tried it. but could it be if i delete the record 'id_samping' on table 'samping', it wont affects the record 'id_samping' on table 'utama'? – hamzbond Dec 11 '19 at 09:06
  • @hamzbond I suspect you're confusing actual foreign keys with columns that happen to contain values that you know they are primary keys in other tables (but MySQL doesn't). – Álvaro González Dec 11 '19 at 09:11
  • yeah.. i actualy just learn database. And i read the mysql references. i read them and try every query and i ended up confusing what the reference says and what i do on my database :( – hamzbond Dec 11 '19 at 09:35
  • @hamzbond: If MySQL recognizes foreign keys then it sets some restrictions and checks on the data of these column to keep the data correct on all places to keep integrity. But if foreign keys are ignored then you can do anything with the data - MySQL does not care. – juergen d Dec 11 '19 at 11:06
  • I see... I tried to make different record on both tables and MySQL didn't turn any error. So this MyISAM table allow me to create foreign key query but dont do anything about foreign key when i make record. – hamzbond Dec 12 '19 at 02:03
  • thankyou @juergend – hamzbond Dec 12 '19 at 02:03