[MySQL] 테이블 간 관계 맺기(primary key, foreign key)

MySQL과 같은 관계형 데이터베이스에서 핵심은 테이블 간 적절히 관계를 맺어주면서 데이터 중복을 최소화하면서 구조를 짜임새있게 만드는 것입니다.  

 

어떤 대학교에 학생 관리 데이터베이스가 있다고 가정하겠습니다. 이 데이터베이스에는 학생 테이블과 성적 테이블이 들어가 있습니다. 학생 테이블은 학번, 이름, 전공 컬럼으로 구성되어 있고, 성적 테이블은 일련번호, 학번, 과목, 점수 컬럼으로 구성되어 있습니다. 다음과 같은 데이터가 두 테이블에 쌓이게 할 것입니다.

 

 

학생 테이블의 경우 다음과 같은 조건을 만족해야 합니다. 

1) 학번은 한 학생에게만 부여되는 고유한 정보입니다. 중복될 수 없습니다. 

2) 이름, 전공 정보는 비어있을 수 없습니다. 

 

성적 테이블은 다음 조건을 만족해야 합니다. 

1) 일련번호는 고유해야 합니다. 중복될 수 없습니다. 그리고 저절로 1씩 증가해야 합니다.

2) 여기서 학번은 해당 과목에 대해 해당 성적을 받은 학생의 학번입니다. 한 학생이 여러 과목을 수강하는 것이 당연하기 때문에 이 테이블에서 학번은 중복될 수 있습니다. 

 

그러면 어떻게 테이블을 만들어줘야할까요? 테이블 생성 쿼리에 대해 각각 살펴보겠습니다. 

 

테이블 생성하기 

학생 테이블

학생 테이블은 다음과 같은 쿼리로 생성하면 됩니다. 

 

CREATE TABLE student (
    student_id CHAR(8) NOT NULL PRIMARY KEY,
    name CHAR(5) NOT NULL,
    major VARCHAR(10) NOT NULL
);

 

우선 모든 컬럼에 NOT NULL 제약 조건을 부여했습니다. 그리고 학번(student_id) 컬럼에는 primary key를 부여했습니다. 고유값이어야 하면서 클러스터형 인덱스가 생성되게 하기 위함입니다. 아무래도 학번을 기준으로 데이터 조회를 많이하겠죠? 인덱스가 부여되어 있어야 조회를 빠르게 해낼 수 있을 것입니다. 

 

성적 테이블

성적 테이블을 만들어보겠습니다.

 

CREATE TABLE grade (
    num INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    student_id CHAR(8) NOT NULL,
    subject_name VARCHAR(10) NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY(student_id) REFERENCES student(student_id)
);

 

성적 테이블 역시 모든 컬럼이 중요한 정보이기 때문에 NOT NULL 제약 조건을 부여했습니다. 그리고 일련번호(num) 컬럼에는 primary key를 부여했습니다. 또한 1씩 자동으로 증가하게 하기 위해서 AUTO_INCREMENT를 지정해줬습니다. 그리고 grade 테이블의 student_id 컬럼은 student 테이블의 student_id를 참조하는 외래 키이기 때문에 마지막 줄과 같이 설정해줬습니다. 참고로 primary key 또는 unique 제약 조건이 설정된 컬럼만 참조의 대상이 될 수 있습니다. 

 

여기서는 학생 테이블이 기준 테이블(부모 테이블)이고, 성적 테이블이 외래 키 테이블(자식 테이블)입니다. 

 

 

 

데이터 삽입하기

이제 각 테이블에 데이터를 삽입하도록 하겠습니다. 먼저 기준 테이블인 학생 테이블에 데이터를 넣어줘야 합니다. 

 

INSERT INTO student VALUES ('11111111', '심교훈', '전자공학');
INSERT INTO student VALUES ('11111122', '문태호', '정보통신공학');
INSERT INTO student VALUES ('11111133', '황병일', '경영학');

 

잘 들어갔는지 확인해보겠습니다. 

 

SELECT * FROM student;

 

데이터가 잘 들어갔네요.

 

 

이제 이 학생들이 입학하여 몇몇 수업에서 받은 점수를 입력해보겠습니다. 

 

INSERT INTO grade (student_id, subject_name, score) VALUES ('11111111', '회로이론', 88);
INSERT INTO grade (student_id, subject_name, score) VALUES ('11111133', '경영학개론', 95);
INSERT INTO grade (student_id, subject_name, score) VALUES ('11111111', '물리전자', 79);
INSERT INTO grade (student_id, subject_name, score) VALUES ('11111122', '임베디드설계', 92);

 

데이터가 잘 들어갔는지 확인해보겠습니다. 

 

SELECT * FROM grade;

 

역시 데이터가 잘 들어갔죠?

 

 

에러 발생시키기 - 부모 테이블에 없는 학번으로 성적 입력 시도

그러면 한번 student 테이블에 없는 학번의 시험 성적을 grade 테이블에 넣어보도록 하겠습니다.

 

INSERT INTO grade (student_id, subject_name, score) VALUES ('11111177', '캡스톤디자인', 85);

 

위 쿼리를 실행했더니 다음과 같은 에러 메시지가 출력되었습니다. 

 

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`tabledb`.`grade`, CONSTRAINT `grade_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)) 

 

해석하자면 student_id가 외래 키로 설정되어 있기 때문에 student 테이블에 없는 학번에 대해서는 입력이 안 된다는 뜻입니다. 이처럼 외래 키를 잘 설정해놓으면 엄한 데이터가 테이블에 삽입되는 것을 막을 수 있습니다. 

 

에러 발생시키기 - 학생 데이터 삭제 시도

이번에는 성적 테이블에 데이터가 남아 있는 학생의 정보를 학생 테이블에서 삭제해보겠습니다. 

 

DELETE FROM student WHERE student_id = '11111111';

 

학번이 11111111인 행을 지우려고 시도했으나 다음과 같은 에러 메시지가 떴습니다. 

 

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`tabledb`.`grade`, CONSTRAINT `grade_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`))

자식 테이블에 사용되고 있는 데이터가 있어서 함부로 부모 테이블의 데이터를 삭제할 수 없다는 뜻입니다. 만약 학생 데이터를 삭제하고 싶다면, 먼저 그 학생과 관련된 자식 테이블의 데이터를 모두 삭제한 후에 다시 진행해야 합니다. 

 

DELETE FROM grade WHERE student_id = '11111111';
DELETE FROM student WHERE student_id = '11111111';

 

이렇게 하면 문제 없이 111111111 학생 정보가 student 테이블에서 잘 제거됩니다. 학생 테이블을 조회해보면 이제 11111111 학생은 남아 있지 않습니다. 

 

 

ON DELETE CASCADE

그런데 위 과정은 데이터의 무결성을 보장한다는 측면에서는 좋지만, 조금 번거로운 면이 있습니다. student 테이블에서 어떤 학생 정보를 제거하면, 저절로 관련된 자식 테이블의 데이터가 삭제되게 해줄 수도 있습니다. 외래 키를 생성할 때 ON DELETE CASCADE 옵션을 추가해주는 것입니다. 이미 테이블을 생성했으므로 외래 키만 다시 설정하도록 하겠습니다. 기존의 외래 키를 삭제한 후에 다시 ON DELETE CASCADE 옵션을 추가해서 외래 키를 생성하면 됩니다. 

 

ALTER TABLE grade DROP FOREIGN KEY grade_ibfk_1;
ALTER TABLE grade ADD CONSTRAINT FOREIGN KEY(student_id) REFERENCES student(student_id) ON DELETE CASCADE;

 

(참고로 외래 키를 만들 때 이름을 지정해주지 않았기 때문에 저의 경우는 grade_ibfx_1이라는 이름이 자동으로 부여되었습니다.) 

 

이렇게 ON DELETE CASCADE 옵션을 부여하고 나니, 이제 성적 테이블에 어떤 학생의 데이터가 남아 있더라도 학생 테이블의 데이터가 잘 삭제되고 또한 그와 연관된 성적 데이터도 같이 제거됩니다. 

 

ON DELETE CASCADE 옵션 말고도 ON UPDATE CASCADE 옵션도 있습니다. 이 옵션을 부여하면 부모 테이블인 student 테이블에서 만약 student_id를 변경했다면, 자식 테이블인 grade 테이블에서도 알아서 매칭되는 student_id가 수정됩니다.  

 

JOIN 시 매칭의 조건으로 활용

보통 자식 테이블의 어떤 컬럼을 부모 테이블의 어떤 컬럼을 참조하는 외래 키로 설정하면 그 컬럼은 두 테이블을 JOIN 할 때 매칭의 기준으로 많이 활용됩니다. 

 

SELECT 
    student.student_id, student.name, grade.subject_name, grade.score 
FROM grade 
JOIN student 
ON grade.student_id = student.student_id;

 

위 SQL 문을 실행하면 다음과 같이 성적을 조회할 때 해당 학번 학생의 이름까지도 함께 확인할 수 있습니다. 

 

 

정리하며

오늘은 테이블 간 관계를 선언해주면서 primary key와 foreign key의 필요성에 대해 살펴봤습니다.

 

primary key로 지정된 컬럼은 고유한 값만 가질 수 있고, 외래 키가 참조하는 대상이 될 수 있습니다. 또한 primary key로 지정된 컬럼을 기준으로 클러스터형 인덱스가 생성됩니다. 

 

foreign key는 해당 테이블이 어떤 테이블과 관계를 맺게 해줍니다. 두 테이블에 공통적으로 들어가 있는 컬럼이 부모 테이블 입장에서는 primary key가 되고, 자식 테이블 입장에서는 foreign key가 됩니다. 

 

제가 이해한 부분에 미흡한 부분이 있다면, 지적해주시면 감사하겠습니다.