database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Tutorials 16508 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 16508
INNODB 와 MYISM TYPE 에서의 Foreign Key 차이
작성자
김순석(astrobio)
작성일
2002-07-15 16:57
조회수
7,225

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 의 차이입니다..

[Top]
No.
제목
작성자
작성일
조회
16567mysql-3.23 for FreeBSD 4.5 (pgcc compile)
정재익
2002-07-22
6761
16566Mysql For FreeBSD4
정재익
2002-07-22
6071
16565mysql root 암호를 잃어버렸을때
정재익
2002-07-22
9052
16508INNODB 와 MYISM TYPE 에서의 Foreign Key 차이
김순석
2002-07-15
7225
16490MySQL 기동시 자동 파일 복구 기능
정재익
2002-07-12
6509
16444MySQL에서 한글 정렬
정재익
2002-07-09
6911
16443해당 날짜에 DB update 하기
정재익
2002-07-09
6164
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.016초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다