Database/maria

foreign-keys

사리생성 2018. 1. 30. 14:02

foreign-keys 의 참조 옵션

참조 : https://mariadb.com/kb/en/library/foreign-keys/ 

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

ON DELETE,ON UPDATE 에 사용할 수 있는 옵션.

  • RESTRICT: 상위 테이블의 변경이 방지됩니다. 명령문은 1451 오류로 종료됩니다 (SQLSTATE'2300 '). ON DELETE 및 ON UPDATE에 대한 기본 동작입니다.
  • NO ACTION: RESTRICT의 동의어.
  • CASCADE: 변경이 허용되며 하위 테이블에 전파됩니다. 예를 들어, 부모 행이 삭제되면 자식 행도 삭제됩니다. 상위 행 ID가 변경되면 하위 행 ID도 변경됩니다.
  • SET NULL: 변경이 허용되며 하위 행의 외부 키 열은 NULL로 설정됩니다.
  • SET DEFAULT: PBXT에서만 작동합니다. SET NULL과 비슷하지만 외래 키 열은 기본값으로 설정되었습니다. 기본값이 없으면 오류가 발생합니다.

테스트를 위한 테이블 생성 및 데이터 등록.
DELETE CASCADE, UPDATE RESTRICT 로 외례키 생성.
삭제시 함께 삭제, 수정시 에러 발생.
CREATE TABLE user (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(1000) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE coin (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(100) NOT NULL,
user_id SMALLINT UNSIGNED NOT NULL,
CONSTRAINT fk_bitcoin_user
FOREIGN KEY (user_id) REFERENCES user (id)
ON DELETE CASCADE -- 부모테이블의 값이 dalete 시에 같이 삭제.
ON UPDATE RESTRICT -- 부모테이블의 값이 update 시에 변경없음.
) ENGINE = InnoDB;

INSERT INTO user (name) VALUES ('John');
INSERT INTO coin (name, user_id) VALUES ('BTC', LAST_INSERT_ID());


INSERT INTO user (name) VALUES ('Mary');
INSERT INTO coin (name, user_id) VALUES ('BCH', LAST_INSERT_ID()),
('ETH', LAST_INSERT_ID()),
('BTC', LAST_INSERT_ID()),

('XRP', LAST_INSERT_ID());

/* update 시엔 아래와 같이 에러 발생

delete 시엔 아래와 같이 함께 삭제. */


UPDATE user SET id = 3 WHERE name = 'Mary';
-- 에러 [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`employees`.`coin`,
-- CONSTRAINT `fk_bitcoin_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE)

DELETE FROM user WHERE id = 1;


SELECT * FROM user;


SELECT * FROM coin

** constraint : 제약조건

** restrict : 얽매다.


FOREIGN KEY을 삭제하고 delete, update 모두 cascade로 다시 생성. 

ALTER TABLE coin DROP FOREIGN KEY fk_bitcoin_user; 
ALTER TABLE coin ADD CONSTRAINT fk_bitcoin_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE;
SHOW CREATE TABLE coin;


업데이트 시 변경 내용 확인.

UPDATE user SET id = 100 WHERE name = 'Mary';
SELECT * FROM user;
SELECT * FROM coin;


이상입니다.


'Database > maria' 카테고리의 다른 글

FIND_IN_SET  (0) 2023.12.28
explain  (0) 2018.01.30
mysql profiling  (0) 2018.01.29
window functions 윈도우 함수.  (0) 2018.01.26
macOS 에 maria db 설치 및 test db 생성  (0) 2018.01.26