|
1. INNODB 와 MYISM TYPE 에서의 차이점을 살펴보겠습니다.
=> INNODB 에서만 Foreign Key가 적용됩니다.
참조) http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#SEC446
Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints to guard the integrity of your data.
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE SET NULL
) TYPE=INNODB;
CREATE TABLE parent1(id INT NOT NULL, PRIMARY KEY (id));
CREATE TABLE child1(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent1(id)
ON DELETE SET NULL
);
mysql> INSERT INTO parent VALUES(\1\);
mysql> INSERT INTO parent VALUES(\2\);
mysql> INSERT INTO parent1 VALUES(\1\);
mysql> INSERT INTO parent1 VALUES(\2\);
mysql> INSERT INTO child VALUES(\2\, \2\);
mysql> INSERT INTO child1 VALUES(\2\, \2\);
mysql> select * from parent;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
mysql> select * from parent1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 2 | 2 |
+------+-----------+
1 row in set (0.00 sec)
mysql> select * from child1;
+------+-----------+
| id | parent_id |
+------+-----------+
| 2 | 2 |
+------+-----------+
1 row in set (0.00 sec)
mysql> DELETE FROM parent WHERE id=\2\;
Query OK, 1 row affected (0.25 sec)
mysql> select * from parent;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> DELETE FROM parent1 WHERE id=\2\;
Query OK, 1 row affected (0.00 sec)
mysql> select * from parent1;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 2 | NULL |
+------+-----------+
1 row in set (0.00 sec)
mysql> select * from child1;
+------+-----------+
| id | parent_id |
+------+-----------+
| 2 | 2 |
+------+-----------+
1 row in set (0.01 sec)
* 위에서 보이듯이 MYISM TYPE 은 Foreign Key 가 적용되지 않습니다.
2. 다음으로 튜플을 지워보겠습니다.
CREATE TABLE parent3(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child3(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent3(id)
ON DELETE CASCADE
) TYPE=INNODB;
mysql> INSERT INTO parent3 VALUES(\1\);
mysql> INSERT INTO parent3 VALUES(\2\);
mysql> INSERT INTO child3 VALUES(\2\, \2\);
mysql> select * from parent3;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
mysql> select * from child3;
+------+-----------+
| id | parent_id |
+------+-----------+
| 2 | 2 |
+------+-----------+
1 row in set (0.01 sec)
mysql> DELETE FROM parent3 WHERE id=\2\;
Query OK, 1 row affected (0.04 sec)
mysql> select * from child3;
Empty set (0.00 sec)
* ON DELETE CASCADE 와 ON DELETE SET NULL 의 차이입니다..
|
