| Oracle/MySQL 관련 게시판 |
|---|
| 2016/06/26(00:52) from 58.125.30.140 | |
| 작성자 : 주인장 | 조회수 : 3642 , 줄수 : 72 |
| Re: [MySQL] foreign key 연습 |
|---|
|
mysql> CREATE TABLE `dept` ( `dept_no` int(11) unsigned NOT NULL, `dept_name` varchar(32) NOT NULL, PRIMARY KEY (`dept_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> CREATE TABLE `emp` ( `emp_no` int(11) unsigned NOT NULL, `dept_no` int(11) unsigned NOT NULL, `emp_name` varchar(32) NOT NULL, PRIMARY KEY (`emp_no`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> alter table emp add constraint fk_dept_no foreign key(dept_no) references dept (dept_no) on delete cascade; mysql> show create table dept; +-------+------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------+| dept | CREATE TABLE `dept` ( `dept_no` int(11) unsigned NOT NULL, `dept_name` varchar(32) NOT NULL, PRIMARY KEY (`dept_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------+1 row in set (0.00 sec) mysql> show create table emp; +-------+------------------------------------------------------------------------------------------+| Table | Create Table | +-------+------------------------------------------------------------------------------------------+| emp | CREATE TABLE `emp` ( `emp_no` int(11) unsigned NOT NULL, `dept_no` int(11) unsigned NOT NULL, `emp_name` varchar(32) NOT NULL, PRIMARY KEY (`emp_no`), KEY `fk_dept_no` (`dept_no`), CONSTRAINT `fk_dept_no` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------+1 row in set (0.00 sec) @ON DELETE CASCADE만 적용해도 ON UPDATE CASCADE 를 적용을 포함한듯함. mysql> update dept set dept_no=1004 where dept_no=1003; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`viewdb`.`emp`, CONSTRAINT `fk_dept_no` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`) ON DELETE CASCADE) @ON DELETE CASCADE만 적용해도 ON UPDATE CASCADE 를 적용을 포함한듯함. mysql> update dept set dept_no=1004 where dept_no=1003; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from emp; +---------+---------+----------+ | emp_no | dept_no | emp_name | +---------+---------+----------+ | 2012001 | 1001 | 한놈 | | 2012002 | 1004 | 두시기 | | 2012003 | 1004 | 석삼 | +---------+---------+----------+ 3 rows in set (0.00 sec) mysql> select * from dept; +---------+-----------+ | dept_no | dept_name | +---------+-----------+ | 1001 | 인사부 | | 1002 | 영업부 | | 1004 | 생산부 | +---------+-----------+ 3 rows in set (0.00 sec) |
|
|