[link/post/mysql] REFERENCE, innodb and foreign-key

foreign-key 가 지원되지 않는 MySQL MyISAM type table 에서 REFERENCE 를 사용하는 방법.

• http://blog.pages.kr/57

여러분이 지금 정의하는 컬럼은 다른 테이블에 있는 컬럼을 참조하도록 의도된 것이라는 것을 알려주는 메모 또는 코멘트 역할만 하게 된다


CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;

+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;

+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+

SELECT s.* FROM person p, shirt s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+

ref)

MySQL Manual 'create table' from. mysqlkorea

reference_definition:

    REFERENCES tbl_name [(index_col_name,...)]

               [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]

               [ON DELETE reference_option]

               [ON UPDATE reference_option]

reference_option:

    RESTRICT | CASCADE | SET NULL | NO ACTION

#InnoDB테이블은 외래 키 제약 조건에 대한 확인을 제공한다. 14.2장 "InnoDB 저장 엔진"부분에 이에 대한 내용이 있다. InnoDB에서 FOREIGN KEY는 이 부분의 시작부에 CREATE TABLE문에 나타난 것보다 제한적이다. 참조 테이블의 컬럼들은 항상 명시적으로 명명 되어야 하며 InnoDB는 ON DELETE와 ON UPDATE 적용을 외래키들에 대해 제공한다. 자세한 사항은 14.2.6.4장 "FOREIGN KEY 제약 사항" 부분을 참조하라. 

다른 저장 엔진들에 대해서는 MySQL서버가 CREATE TABLE 문에서 FOREIGN KEY와 REFERENCE문은 분석하나 무시 한다. CHECK문은 모든 저장 엔진에 의해 무시 되며 1.9.5.5장 "외래키" 부분을 참조 하라.