Foreign Key Constraints

l  FOREIGN KEY : 다른 테이블의 PRIMARY KEY를 참조합니다.

 

	Delete	Update
Restrict	Ö	Ö
Cascade	Ö	Ö
Default	Ö	Ö
Nullify	Ö	Ö

 

 

1.     dept 테이블을 생성합니다.

use test;

mysql> CREATE TABLE dept

    -> (dept_id CHAR(2) NOT NULL,

    -> name varchar(20),PRIMARY KEY(dept_id)) engine=INNODB;

 

2. 행을 입력합니다.

mysql> INSERT INTO dept VALUES ('D1', 'dept1');

1 row created.

mysql> select * from dept;

+---------+-------+

| dept_id | name  |

+---------+-------+

| D1      | dept1 |

+---------+-------+

 

3. PK 에 동일한 D1을 입력하면, 제약조건 위반으로 오류가 발생합니다.

mysql> INSERT INTO dept VALUES ('D1', 'dept1');

ERROR 1062 (23000): Duplicate entry 'D1' for key 'PRIMARY'

 

4. 두번째 테이블 emp를 생성합니다.

 

mysql> CREATE TABLE emp

    ->     ( id  INT NOT NULL,

    ->         name VARCHAR(20)  NOT NULL,

    ->         salary DECIMAL(7,2),

    ->         dept_id CHAR(2)  NOT NULL,

    ->         PRIMARY KEY (id),

    ->        CONSTRAINT emp_fk FOREIGN KEY (dept_id)

    ->      REFERENCES dept(dept_id) ON DELETE CASCADE)

    -> ENGINE=INNODB;

 

5. 제약조건을 데이터딕션너리에서 조회합니다.

mysql> select CONSTRAINT_NAME, CONSTRAINT_TYPE

    -> from information_schema.table_constraints

    -> where table_name='emp';

+-----------------+-----------------+

| CONSTRAINT_NAME | CONSTRAINT_TYPE |

+-----------------+-----------------+

| PRIMARY         | PRIMARY KEY     |

| emp_fk          | FOREIGN KEY     |

+-----------------+-----------------+

2 rows in set (0.00 sec)

 

6. foreign key 제약조건을 위배하므로 오류가 발생합니다.

mysql> INSERT INTO emp VALUES (300, 'Test3', 1000, 'D3');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`emp`, CONSTRAINT `emp_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`) ON DELETE CASCADE)

 

:Dept 테이블에 존재하지 않는 코드이기 때문입니다.

 

7. foreign key를 만족하는 D1을 입력하면, 잘 저장됩니다.

mysql> INSERT INTO emp VALUES (100, 'Test1', 2000, 'D1');

 

 

8. 이번에는 parent row 삭제시, child 테이블의 row도 동시에 삭제 됩니다.

mysql> select * from dept;

+---------+-------+

| dept_id | name  |

+---------+-------+

| D1      | dept1 |

+---------+-------+

1 row in set (0.00 sec)

 

mysql> select * from emp;

+-----+-------+---------+---------+

| id  | name  | salary  | dept_id |

+-----+-------+---------+---------+

| 100 | Test1 | 2000.00 | D1      |

+-----+-------+---------+---------+

1 row in set (0.00 sec)

 

mysql> DELETE FROM dept WHERE dept_id = 'D1';

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from emp;

Empty set (0.00 sec)

 

 

n  Next-Key Locking

-      Phantom problem을 방지하기 위하여 사용됨

-      해당 인덱스행 +  [바로 직전 | 바로 직후] 행을 동시에 locking

1^st 세션)

mysql> use test;

 

mysql> create table child(id INT);

 

mysql> insert into child values(90),(100),(102);

Query OK, 3 rows affected (0.00 sec)

 

mysql> select @@tx_isolation;

+-----------------+

| @@tx_isolation  |

+-----------------+

| REPEATABLE-READ |

+-----------------+

 

 

1. 트랜잭션을 시작합니다.

mysql> start transaction;

 

mysql> select * from child where id > 100  FOR UPDATE;

+------+

| id   |

+------+

|  102 |

+------+

2^nd 세션)
mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into child values(101);
: hang상태에 빠짐 이때 1^st 세션에서 lock conflict이 관찰 됩니다.

1^st 세션)
select lock_trx_id,lock_mode,lock_type,lock_table,lock_data from  information_schema.innodb_locks;

+-------------+-----------+-----------+----------------+------------------------+

| lock_trx_id | lock_mode | lock_type | lock_table     | lock_data              |

+-------------+-----------+-----------+----------------+------------------------+

| 361C        | X         | RECORD    | `test`.`child` | supremum pseudo-record |

| 361B        | X         | RECORD    | `test`.`child` | supremum pseudo-record |

+-------------+-----------+-----------+----------------+------------------------+

2 rows in set (0.00 sec)