CREATE FOREIGN KEY (FK) DIY FROM ENGINE MYSQL 'MYISAM'

MyISAM is an abbreviation of " MySQL Indexed Sequential Access Method". MyISAM has some drawbacks compared to InnoDB. Therefore, here I will try to overcome one of the shortcomings that exist in MyISAM is no foreign key in this engine. For more details, refer all right myexplanation below:

1. The first step to be done is to create a database testing, for example with the name testdb.

Command: CREATE DATABASE testdb;

2. The second step, namely : create three tables.

# Table ERROR_MSG useful to accommodate the error message.
# Table Log in handy as the child table.
# User table useful as a parent table

Command:

Table error_msg


CREATE TABLE `error_msg` (
`error_msg` VARCHAR(50) NULL DEFAULT NULL
)
ENGINE=MyISAM;

Insert error_msg values ('Data sedang digunakan tabel lain');

Insert error_msg values ('Data tidak ada dalam parameter');



Table login (Table Anak)

CREATE TABLE `login`(

`username` VARCHAR(50) NOT NULL, 

`password` VARCHAR(50) NOT NULL,

`id_user` INT(11) NOT NULL,

PRIMARY KEY (`username`)

)

ENGINE=MyISAM;



INSERT INTO `testdb`.`login` (`username`, `password`, `id_user`)
VALUES ('SWW', '123', 1);

Table user (Table Induk)


CREATE TABLE `user` (

`id_user` INT(11) NOT NULL AUTO_INCREMENT,

`nama` VARCHAR(50) NULL DEFAULT NULL,

PRIMARY KEY (`id_user`)

)

COLLATE='latin1_swedish_ci'

ENGINE=MyISAM

AUTO_INCREMENT=2;



INSERT INTO `testdb`.`user` (`id_user`, `nama`) VALUES (1, 'HUSNIAH');

INSERT INTO `testdb`.`user` (`id_user`, `nama`) VALUES (2, 'RANUAH');


2. The third step , namely : make 4 pieces trigger .

Trigger delete parent table
CREATE TRIGGER `delete_induk` BEFORE DELETE ON `user` FOR EACH ROW BEGIN
IF(SELECT COUNT(*) FROM login WHERE login.id_user = old.id_user)>0 THEN 
insert error_msg VALUES('Data sedang digunakan tabel lain');
END IF;
END