MySQL foreign key 예제

SQL 엔진에 따른 참조무결성 여부 확인 및 테이블 간에 참조 무결성 확인

##########################################################################
# 테이블 삭제 순서가 중요하다.
# DB 엔진을 MyISAM 으로 했을 경우와 InnoDB 로 했을 경우 차이점을 살펴본다.
 
DROP TABLE IF EXISTS buy, person;
CREATE TABLE person (
  userID varchar(20) NOT NULL COMMENT '사용자ID',
  userNM varchar(30) NOT NULL COMMENT '이름',
  mobileNO varchar(60) NOT NULL COMMENT '휴대폰번호',
  reg_date timestamp NOT NULL DEFAULT current_timestamp() COMMENT '등록일자',
  display tinyint(2) NOT NULL DEFAULT 1,
  PRIMARY KEY (userID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO person (userID, userNM, mobileNO) VALUES
('20221001', '홍길동', '010-1000-1001'),
('20221002', '이순신', '010-1000-1003'),
('20221003', '강감찬', '010-1000-1004'),
('20221004', '신시아', '010-1000-1005'),
('20221005', '나현우', '010-1000-1006'),
('20221006', '홍석천', '010-2000-1011'),
('20221007', '김남일', '010-2000-1012'),
('20221008', '김남길', '010-2000-1014'),
('20221009', '박나래', '010-2000-1015');
 
-- ALTER TABLE person ADD PRIMARY KEY (userID);
 
CREATE TABLE buy (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  userID varchar(20) NOT NULL,
  prod_name varchar(20) NOT NULL,
  FOREIGN KEY (userID) REFERENCES person (userID)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
INSERT INTO buy VALUES(NULL,'20221001','지갑');
INSERT INTO buy VALUES(NULL,'20221001','맥북');
INSERT INTO buy VALUES(NULL,'20221003','USB');
INSERT INTO buy VALUES(NULL,'20221004','가방');
INSERT INTO buy VALUES(NULL,'20221005','휴대폰');
INSERT INTO buy VALUES(NULL,'20221005','의류');
INSERT INTO buy VALUES(NULL,'20221005','운동화');
INSERT INTO buy VALUES(NULL,'20221006','노트북');
 
SELECT p.userID, p.userNM, b.prod_name 
FROM buy b INNER JOIN person p ON b.userID = p.userID;
 
UPDATE person SET userID='20221010' WHERE userID='20221001';
-- 에러가 발생하는지 확인한다.
 
##########################################################################
DROP TABLE IF EXISTS buy, person;
CREATE TABLE person (
  userID varchar(20) NOT NULL COMMENT '사용자ID',
  userNM varchar(30) NOT NULL COMMENT '이름',
  mobileNO varchar(60) NOT NULL COMMENT '휴대폰번호',
  reg_date timestamp NOT NULL DEFAULT current_timestamp() COMMENT '등록일자',
  display tinyint(2) NOT NULL DEFAULT 1,
  PRIMARY KEY (userID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO person (userID, userNM, mobileNO) VALUES
('20221001', '홍길동', '010-1000-1001'),
('20221002', '이순신', '010-1000-1003'),
('20221003', '강감찬', '010-1000-1004'),
('20221004', '신시아', '010-1000-1005'),
('20221005', '나현우', '010-1000-1006'),
('20221006', '홍석천', '010-2000-1011'),
('20221007', '김남일', '010-2000-1012'),
('20221008', '김남길', '010-2000-1014'),
('20221009', '박나래', '010-2000-1015');
 
-- ALTER TABLE person ADD PRIMARY KEY (userID);
 
CREATE TABLE buy (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  userID varchar(20) NOT NULL,
  prod_name varchar(20) NOT NULL,
  FOREIGN KEY (userID) REFERENCES person (userID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO buy VALUES(NULL,'20221001','지갑');
INSERT INTO buy VALUES(NULL,'20221001','맥북');
INSERT INTO buy VALUES(NULL,'20221003','USB');
INSERT INTO buy VALUES(NULL,'20221004','가방');
INSERT INTO buy VALUES(NULL,'20221005','휴대폰');
INSERT INTO buy VALUES(NULL,'20221005','의류');
INSERT INTO buy VALUES(NULL,'20221005','운동화');
INSERT INTO buy VALUES(NULL,'20221006','노트북');
 
SELECT p.userID, p.userNM, b.prod_name 
FROM buy b INNER JOIN person p ON b.userID = p.userID;
 
UPDATE person SET userID='20221010' WHERE userID='20221001';
-- 에러가 발생하는지 확인한다.
 
DELETE FROM person WHERE userID='20221001';
-- 에러가 발생하는지 확인한다.
 
##########################################################################
-- 기준 테이블의 열이 변경되는 경우 : 회원 테이블의 userID 변경
-- alter table [추가할테이블명] add constraint [제약조건명] foreign key(컬럼명) 
  references [부모테이블명] (PK컬럼명) [ON DELETE CASCADE / ON UPDATE CASECADE];
 
DROP TABLE IF EXISTS buy, person;
CREATE TABLE person (
  userID varchar(20) NOT NULL COMMENT '사용자ID',
  userNM varchar(30) NOT NULL COMMENT '이름',
  mobileNO varchar(60) NOT NULL COMMENT '휴대폰번호',
  reg_date timestamp NOT NULL DEFAULT current_timestamp() COMMENT '등록일자',
  display tinyint(2) NOT NULL DEFAULT 1,
  PRIMARY KEY (userID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO person (userID, userNM, mobileNO) VALUES
('20221001', '홍정민', '010-1000-1001'),
('20221002', '이순신', '010-1000-1003'),
('20221003', '강감찬', '010-1000-1004'),
('20221004', '신시아', '010-1000-1005'),
('20221005', '나현우', '010-1000-1006'),
('20221006', '홍석천', '010-2000-1011'),
('20221007', '김남일', '010-2000-1012'),
('20221008', '김남길', '010-2000-1014'),
('20221009', '박나래', '010-2000-1015');
 
CREATE TABLE buy (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  userID varchar(20) NOT NULL,
  prod_name varchar(20) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
ALTER TABLE buy 
  ADD constraint fk_userID 
  FOREIGN KEY(userID) REFERENCES person (userID) 
  ON UPDATE CASCADE 
  ON DELETE CASCADE;
 
INSERT INTO buy VALUES(NULL,'20221001','지갑');
INSERT INTO buy VALUES(NULL,'20221001','맥북');
INSERT INTO buy VALUES(NULL,'20221003','USB');
INSERT INTO buy VALUES(NULL,'20221004','가방');
INSERT INTO buy VALUES(NULL,'20221005','휴대폰');
INSERT INTO buy VALUES(NULL,'20221005','의류');
INSERT INTO buy VALUES(NULL,'20221005','운동화');
INSERT INTO buy VALUES(NULL,'20221006','노트북');
 
SHOW INDEX FROM buy;
 
UPDATE person SET userID='20221010' WHERE userID='20221001';
 
DELETE FROM person WHERE userID='20221003';
 
-- foreign key 확인
select * from information_schema.table_constraints where table_name = 'buy';
 
-- foreign key 삭제
ALTER TABLE buy 
  DROP foreign key fk_userID;
 
-- foreign key 삭제 확인
select * from information_schema.table_constraints where table_name = 'buy';
 
use test;
SHOW INDEX FROM buy;
 
DELETE FROM person WHERE userID='20221004';
 
###############################################################################