- mysql -u root -p <Database Name> < <sql script>
- mysql> show variables like ‘%innodb%’;
+—————+—————-+
| Variable_name | Value |
| have_innodb | DISABLED |
+—————+—————-+
- edit /etc/my.cnf or /etc/my.ini, under [mysqld] tag, add the following line and restart MySQL:
innodb_data_file_path=ibdata1:10M:autoextend
- mysql> show variables like ‘%innodb%’;
+—————+—————-+
| Variable_name | Value |
| have_innodb | YES |
+—————+—————-+
- In the table definiation, add:
FOREIGN KEY (`Id`) REFERENCES UserEntity (`Id`) ON DELETE CASCADE
- In the table definiation, define TABLE as “TYPE=InnoDB;”
- Following is a complete example:
drop table if exists Rooms;
drop table if exists Accounts;
drop table if exists Invites;CREATE TABLE `Rooms` (
`RoomId` mediumint(8) unsigned NOT NULL default ‘0’,
PRIMARY KEY (`RoomId`)) TYPE=InnoDB;CREATE TABLE Accounts (
`AccountId` mediumint(8) unsigned NOT NULL default ‘0’,
PRIMARY KEY (`AccountId`)) TYPE=InnoDB;CREATE TABLE `Invites` (
`InviteId` mediumint(8) unsigned NOT NULL auto_increment,
`Inviter` mediumint(8) unsigned NOT NULL default ‘0’,
`AccountId` mediumint(8) unsigned NOT NULL default ‘0’,
`RoomId` mediumint(8) unsigned NOT NULL default ‘0’,
`OnDate` datetime NOT NULL default ‘0000-00-00 00:00:00’,
PRIMARY KEY (`InviteId`),
KEY `RoomId` (`RoomId`),
KEY `Inviter` (`Inviter`),
KEY `AccountId` (`AccountId`),
FOREIGN KEY (`RoomId`) REFERENCES `Rooms` (`RoomId`) ON DELETE CASCADE,
FOREIGN KEY (`AccountId`) REFERENCES `Accounts` (`AccountId`),
FOREIGN KEY (`Inviter`) REFERENCES `Accounts` (`AccountId`)
) TYPE=InnoDB PACK_KEYS=1;INSERT INTO Accounts VALUES (100);
INSERT INTO Accounts VALUES (101);
INSERT INTO Rooms VALUES (10);
INSERT INTO Invites VALUES (NULL, 100, 101, 10, NOW());
INSERT INTO Invites VALUES (NULL, 101, 100, 10, NOW());
mysql> select * from Rooms;
+——–+
| RoomId |
| 10 |
+——–+
1 row in set (0.00 sec)mysql> select * from Invites;
+———-+———+———–+——–+———————+
| InviteId | Inviter | AccountId | RoomId | OnDate |
| 1 | 100 | 101 | 10 | 2002-09-15 03:53:23 |
| 2 | 101 | 100 | 10 | 2002-09-15 03:53:23 |
+———-+———+———–+——–+———————+
2 rows in set (0.00 sec)mysql>
mysql>
mysql> delete from Rooms where RoomId = 10;
Query OK, 1 row affected (0.08 sec)mysql> select * from Invites;
Empty set (0.00 sec)mysql> select * from Rooms;
Empty set (0.00 sec)
Load sql script in MySql
Enable “ON DELETE CASCADE”