테이블의 구성: 필드와 레코드
RDBS 내의 레코드들은 다음과 같이 테이블의 형태를 이룬다. 이때 각 필드로 사용 가능한 데이터 유형이 정해져 있다. 이를 필드 타입이라고 한다. 그리고 테이블 내의 특정 레코드를 식별할 수 있는 필드의 집합인 키라고 한다. 키는 레코드의 식별뿐만 아니라 데이터 간의 참조에도 사용된다. 즉, 필드 타입과 키를 이해하면 레코드를 구성하는 데이터의 유형을 이해할 수 있고, 테이블 내 특정 레코드를 식별할 수 있다.
이미지 출처: https://93jpark.tistory.com/23
필드 타입
RDBMS의 테이블 필드에는 다양한 데이터 형식(타입), 즉 필드 타입이 저장될 수 있다. 필드 종류의 타입의 종류는 DBMS마다 조금씩 차이가 있지만, 큰 틀에서는 크게 다르지 않다. 다음은 MySQL을 기준으로 정리한 대표적인 필드 타입이다. 테이블 내 레코드는 다음과 같은 데이터 유형으로 구성될 수 있다.
숫자형
데이터 유형 | 정의 |
TINYINT(n) | 정수형 데이터 타입(1byte)
-128 ~ +127 또는 0 ~ 255수 표현할 수 있다. |
SMALLINT(n) | 정수형 데이터 타입(2byte)
-32768 ~ 32767 또는 0 ~ 65536수 표현할 수 있다. |
MEDIUMINT(n) | 정수형 데이터 타입(3byte)
-8388608 ~ +8388607 또는 0 ~ 16777215수 표현할 수 있다. |
INT(n) | 정수형 데이터 타입(4byte)
-2147483648 ~ +2147483647 또는 0 ~ 4294967295수 표현할 수 있다. |
BIGINT(n) | 정수형 데이터 타입(8byte)
- 무제한 수 표현할 수 있다. |
FLOAT(길이, 소수) | 부동 소수형 데이터 타입(4byte)
-고정 소수점을 사용 형태이다. |
DECIMAL(길이, 소수) | 고정 소수형 데이터 타입고정(길이+1byte)
-소수점을 사용 형태이다. |
DOUBLE(길이, 소수) | 부동 소수형 데이터 타입(8byte)
-DOUBLE을 문자열로 저장한다. |
문자형
데이터 유형 | 정의 |
CHAR(n) | 고정 길이 데이터 타입(최대 255byte)
- 지정된 길이보다 짦은 데이터 입력될 시 나머지 공간 공백으로 채워진다. |
VARCHAR(n) | 가변 길이 데이터 타입(최대 65535byte)
- 지정된 길이보다 짦은 데이터 입력될 시 나머지 공간은 채우지 않는다. |
TINYTEXT(n) | 문자열 데이터 타입(최대 255byte) |
TEXT(n) | 문자열 데이터 타입(최대 65535byte) |
MEDIUMTEXT(n) | 문자열 데이터 타입(최대 16777215byte) |
LONGTEXT(n) | 문자열 데이터 타입(최대 4294967295byte) |
날짜/시간형
데이터 유형 | 정의 |
DATE | 날짜(년도, 월, 일) 형태의 기간 표현 데이터 타입(3byte) |
TIME | 시간(시, 분, 초) 형태의 기간 표현 데이터 타입(3byte) |
DATETIME | 날짜와 시간 형태의 기간 표현 데이터 타입(8byte) |
TIMESTAMP | 날짜와 시간 형태의 기간 표현 데이터 타입(4byte)
-시스템 변경 시 자동으로 그 날짜와 시간이 저장된다. |
YEAR | 년도 표현 데이터 타입(1byte) |
기타
데이터 유형 | 정의 |
ENUM | 정해진 문자열 값들 중 하나(예: 남, 여) |
GEOMETRY | 위치 정보나 도형(점, 선, 면 등) |
XML | 구조화된 마크업 데이터 타입 |
JSON | JSON 문자열 데이터 타입 |
키
다음 테이블을 한번 살펴보자. 각 레코드는 어떤 필드(들)로 식별할 수 있을지 생각해보자. 첫 번째 테이블에서는 ‘교수 이름’과 ‘과목 이름’의 조합으로 각 레코드를 식별할 수 있고, 두 번째 테이블에서는 ‘글 ID’로 식별하거나 ‘글 제목’과 ‘작성 날짜’의 조합으로 식별할 수도 있을 것이다.
교수 이름 | 과목 이름 | 학생 수 |
Kim | 자료구조 | 20 |
Lee | 운영체제 | 30 |
Park | 컴퓨터구조 | 20 |
Kim | 데이터베이스 | 30 |
Park | 데이터베이스 | 30 |
글 ID | 글 제목 | 작성 글 | 작성자 | 작성 날짜 |
1 | 오늘 일기 | 오늘은 밥을 먹었다. 어디를 놀러 갔는데… | Kim | 2021-01-12 |
2 | 독후감 | 오늘은 C++ 책을 리뷰해 볼게요… | Lee | 2021-01-15 |
3 | 영화 후기 | 오늘은 어벤져스 영화를 리뷰해 보겠습니다… | Park | 2021-01-20 |
이렇게 테이블의 레코드를 식별할 수 있는 하나 이상의 필드를 키(key)라고 한다. 키는 테이블의 각 레코드를 식별하는 용도로 사용되기도 하지만, 테이블 간의 참조를 위해 사용되기도 하고 테이블의 접근 속도를 높이기 위해 사용되기도 한다. 많은 전공 서적에서는 다음과 같이 다양한 키의 종류를 설명하지만, 실무에서 키의 모든 종류를 동등한 빈도로 사용하지는 않기 때문에 모든 종류를 암기하지는 않아도 된다.
후보키 (candidate key)
후보 키는 테이블의 한 레코드를 식별하기 위한 필드의 최소 집합을 의미한다. 특정 레코드를 유일하게 식별한다는 점에서 ‘유일성을 갖추었다’고 표현한다. 후보 키는 하나 이상의 필드로 구성될 수 있으며, 후보 키에 포함된 필드 중 하나라도 생략하면 레코드를 고유하게 식별할 수 없게 된다. 이때 불필요한 필드가 키에 포함되어 있지 않고, 최소한의 정보로 레코드를 식별한다는 점에서 ‘최소성을 갖추었다’고도 표현한다. 즉, 후보 키는 유일성과 최소성을 모두 만족하는 키이다.
후보 키는 테이블 내에 하나 이상 존재할 수 있다. 예를 들어 다음과 같은 필드로 구성된 학생 테이블이 있다고 가정해보자. ‘학번, 이메일, 전화번호’가 고유한 값이라면 ‘학번’, ‘이메일’, ‘전화번호’ 각각이 후보 키로 간주될 수 있다.
학번 | 이름 | 이메일 | 전화번호 |
2021001 | Kim | kim@example.com | 010-1234-5678 |
2021002 | Lee | lee@example.com | 010-2345-6789 |
2021003 | Park | park@example.com | 010-3456-7890 |
2021004 | Kang | kang@example.com | 010-5678-4145 |
2021005 | Choi | choi@example.com | 010-2353-2452 |
그렇다면 {학번, 과목 코드, 학년, 수강 연도, 점수}라는 필드로 구성된 ‘성적’이라는 테이블이 있다고 가정해 보자. 여기서 후보 키는 {학번, 과목 코드}가 된다. 최소 이 2개의 필드로 레코드를 식별할 수 있기 때문이다. 이처럼 두 필드 이상으로 구성된 후보 키를 복합키(composite key)라고 부른다.
학번 | 과목 코드 | 학년 | 수강 연도 | 점수 |
2021001 | CS101 | 1 | 2021 | 76 |
2021002 | CS101 | 1 | 2021 | 89 |
2021003 | CS102 | 1 | 2021 | 57 |
2021004 | CS103 | 1 | 2021 | 85 |
2021005 | CS102 | 1 | 2021 | 46 |
반면, 레코드를 식별하기 위한 ‘필드의 최소 집합’이 아닌 레코드를 식별하기 위한 ‘필드의 집합’은 슈퍼키(super key)라고 한다. 즉, 슈퍼 키는 유일성만 만족하고 최소성을 만족하지 않는다. 따라서 슈퍼키에는 레코드 식별에 꼭 필요하지 않은 필드도 포함될 수 있다. 예를 들면 {학번, 과목 코드, 학년}이 슈퍼키가 될 수 있다. 학년은 레코드를 식별하기 위해 꼭 필요한 필드가 아니기 때문이다.
기본키(primary key, PK)
기본키는 한 레코드를 식별하도록 선정되어 테이블당 하나만 존재할 수 있는 키를 말한다. 여러 후보 키 중에서 테이블의 레코드를 대표하도록 선택된 키이다. 후보 키의 일부이기 때문에 유일성과 최소성을 모두 만족하며, 여러 필드로 구성된 기본 키도 존재할 수 있다. 기본 키는 중복된 값이 없어야 하고 반드시 값이 존재해야 하며, 레코드를 구분하기 위한 최소한의 정보만으로 이루어져 있어야 한다. 이때 ‘값이 존재하지 않음’은 NULL이라는 값으로 표기하는데 기본 키는 NULL 값을 가질 수 없다. 앞선 예시로 들었던 학생 테이블에서는 후보 키들 중 ‘학번’ 필드를 기본 키로 삼을 수 있다. 참고로, 기본 키가 아닌 후보 키는 대체키(alternate key)라고 한다. 기본 키 선정 이후에 남은 후보 키가 곧 대체키이다.
외래키(foreign key, FK)
외래키는 다른 테이블의 기본 키를 참조하는 필드로, 테이블 간의 참조 관계를 형성할 때 사용하는 키이다. 가령 다음과 같은 ‘학생’ 테이블과 ‘성적’ 테이블에서 ‘성적’ 테이블의 ‘학번’ 필드는 ‘학생’ 테이블의 ‘학번’을 참조하는 외래 키로 활용할 수 있다.
학번 | 이름 |
2021001 | Kim |
2021002 | Lee |
2021003 | Park |
2021004 | Kang |
2021005 | Choi |
학번 | 과목 코드 | 성적 |
2021001 | CS101 | 76 |
2021002 | CS101 | 89 |
2021003 | CS102 | 57 |
2021004 | CS103 | 85 |
2021005 | CS102 | 46 |
테이블의 관계
이미지 참고: https://nowes00.tistory.com/10
앞서 외래키를 통해 테이블 간 참조가 가능하다고 했다. 이때 외래 키를 매개로 하는 테이블 간 연관 관계에도 여러 종류가 있다. 대표적으로 일대응 대응 관계, 일대다 대응 관계, 다대다 대응 관계로 나눌 수 있다. 각 관계가 무엇을 의미하는지 사례를 통해 하나씩 알아보자.
일대일 대응 관계
RDBMS에서 테이블 간의 일대일 대응 관계는 하나의 레코드가 다른 테이블의 레코드 하나에만 대응되는 경우를 의미한다. 대표적으로는 ‘여권’ 테이블과 ‘승객’ 테이블을 들 수 있다. ‘여권’ 테이블은 ‘고객 번호’를 외래 키 삼아 ‘승객’ 테이블을 참조하고 있다. 이때 두 테이블은 일대일 대응 관계라고 할 수 있다. 한 사람당 오직 하나의 여권만 가질 수 있기 때문이다. 다시 말해, ‘여권’ 테이블에 속한 엔티티 하나는 ‘승객’ 테이블에 속한 엔티티 하나에 대응될 수 있다.
여권 번호 | 고객 번호 | 발급 날짜 |
123456 | 1 | 2021-01-01 |
234564 | 2 | 2021-01-02 |
34567 | 3 | 2021-01-03 |
고객 번호 | 이름 |
1 | 홍길동 |
2 | 박철수 |
3 | 김개똥 |
일대다 대응 관계
테이블 간의 일대다 대응 관계는 하나의 레코드가 다른 테이블의 여러 레코드와 대응될 수 있는 경우를 의미한다. 다음과 같이 연관된 2개의 테이블 ‘주문’ 테이블과 ‘고객’ 테이블이 있다고 가정해보자. 이 경우, 한 고객은 여러 건의 주문을 할 수 있지만, 각 주문은 한 명의 고객에게만 속한다. 따라서, 두 테이블은 일대다 대응 관계라고 볼 수 있다.
고객 ID | 이름 |
1 | 홍길동 |
2 | 김철수 |
주문 ID | 고객 ID | 주문 날짜 |
1001 | 1 | 2024-05-20 |
1002 | 1 | 2024-05-21 |
1003 | 2 | 2024-05-22 |
다대다 대응 관계
테이블 간의 다대다 대응 관계는 한 테이블의 여러 레코드가 다른 테이블의 여러 레코드와 대응되는 경우를 의미한다. 예를 들어, 학생과 강좌간의 관계에서 한 학생이 여러 강좌를 수강하고, 한 강좌에는 여러 학생이 수강할 수 있는 것이 다대다 대응 관계이다.
이미지 출처: https://tmd8633.tistory.com/30
위의 연관관계를 통해 완성된 테이블을 만들어보면 다음과 같다. 이때 테이블에는 기본키(PK)를 포함해서 준복이 발생했고, 데이터의 무결성이 손상되었다. 이로 인해 학번만으로 데이터를 구분할 수 없어졌다. 또 데이터의 추가 또는 변경이 일어난다면 중복된 데이터를 모두 수정 및 추가해야한다. 예를 들어, 홍길동 학생의 학과가 ‘수학과’로 변경된다면 2군데 모두 수정해야하고, 학생 테이블에 성별을 추가한다면 중복데이터를 추가해야하는 일이 발생한다.
이미지 출처: https://tmd8633.tistory.com/30
이는 학생과 과목테이블 사이에 수강테이블을 만들어 다대다 관계를 일대다, 다대일 대응 관계로 풀어주는 것으로 해결할 수 있다. 이때 만들어지는 수강 테이블은 중간 테이블 또는 연결 테이블이라고 부른다.
무결성 제약 조건
무결성이란 일관되며 유효한 데이터의 상태를 말한다. 즉, 무결성 제약 조건이란 데이터베이스에 저장된 데이터의 일관성과 유효성을 유지하기 위해 마땅히 지켜야 하는 조건을 의미한다. 무결성 제약 조건을 지키지 않을 경우 다음과 같은 오류 메시지를 보게 된다.
무결성 제약 조건에는 대표적인 종류 4가지가 있다. 각 제약 조건의 상황을 이해하는 데 집중해 읽어보자.
1. 도메인 제약 조건
도메인 제약 조건(domain constraint)은 테이블이 가질 수 있는 필드 타입과 범위에 대한 규칙이다. 각각의 필드 데이터는 원자 값을 가져야 하고, 지정된 필드 타이블 준수해야 하며, 값의 범위나 기본값이 지정되었을 경우 그를 따라야 한다. 또한 NULL이 허용되지 않았다면 NULL이 저장되어서는 안 된다. 이러한 조건이 명시된 테이블에서 제약 조건을 위배하는 데이터가 삽입/수정될 경우 도메인 제약 조건에 위배된 것으로 간주한다.
원자 값(atomic value)
원자 값이란 더 이상 쪼갤 수 없는 단일한 값을 말한다. 즉, 테이블의 각 필드 데이터는 더 이상 쪼갤 수 없는 단일한 값이어야 한다. 가령 다음 테이블의 필드 데이터인 ‘이름, 나이, 성별’은 각각 더 이상 쪼갤 수 없는 단일한 값이므로 원자 값이라고 할 수 있다.
이름 | 나이 | 성별 |
홍길동 | 45 | 남 |
홍길순 | 34 | 여 |
김개똥 | 27 | 남 |
반면, 다음과 같은 테이블의 필드 데이터는 더 많은 유형으로 쪼개질 여지가 있기 때문에 원자 값이라고 보기 어렵다. 한눈에 보아도 데이터의 유지보수, 검색이 어려워 보인다. 따라서 DBMS는 도메인 제약 조건을 지키기 위해 필드 데이터가 원자 값을 유지하도록 한다.
이름과 나이와 성별 |
홍길동, 45, 남 |
홍길순, 34, 여 |
김개똥, 27, 남 |
2. 키 제약 조건
키 제약 조건(key constriant)은 레코드를 고유하게 식별할 수 있는 키로 지정된 필드에 중복된 값이 존재해서는 안 된다는 제약 조건이다.
3. 엔티티 무결성 제약 조건
엔티티 무결성 제약 조건(entity intergrity constraint)이란 기본 키로 지정한 필드는 고유한 값이어야 하며 NULL이 되어서는 안 된다는 규칙이다. 기본 키와 관련한 제약 조건이므로 기본 키 제약 조건이라고도 부른다. 즉, 레코드를 식별할 수 있는 기본 키가 갖추어야 할 조건을 나타낸다.
4. 참조 무결성 제약 조건
참조 무결성 제약조건(referential integrity constraint)은 외래 키를 통해 다른 테이블을 참조할 때 데이터의 일관성을 지키기 위한 제약 조건으로, 외래 키는 참조하는 테이블의 기본 키와 같은 값을 갖거나 NULL 값을 가져야 한다는 규칙이다. 외래 키와 관련한 제약 조건이므로 외래 키 제약 조건이라고도 부른다.
참조하는 테이블이 삭제되거나 수정되는 경우
참조하고 있는 테이블(부모 테이블)의 데이터가 삭제되거나 수정된다면, 참조하는 테이블(자식 테이블)의 데이터는 어떻게 될까? 예를 들어, 학생 테이블이 학과 테이블의 학과 id를 외래 키로 참조하고 있다고 가정해보자. 만약 학과 테이블에서 특정 학과 id를 삭제한다면, 그 값을 참조하고 있던 학생들의 정보는 어떻게 될까? 이런 상황을 방지하고 무결성을 유지하기 위해, RDBMS는 크게 다음과 같은 4가지의 동작을 취할 수 있다.
1.
CASCADE (연쇄 삭제/수정)
•
부모 테이블에서 삭제되거나 수정된 값을 자식 테이블에도 같이 반영한다.
•
예를 들어, 학과 테이블에서 학과 id가 10인 학과를 삭제하면, 학생 테이블에서 학과 10을 가진 학생 정보도 함께 삭제된다.
2.
SET NULL
•
부모 테이블의 데이터가 삭제되거나 수정되면, 자식 테이블의 외래 키 값을 NULL로 수정한다.
3.
SET DEFAULT
•
외래 키 값을 기본값으로 설정한다. (실제 사용은 드물다고 한다.)
4.
RESTRICT / NO ACTION
•
부모 테이블의 데이터를 삭제하거나 수정하려고 할 때, 자식 테이블에서 참조 중이라면 작업을 거부한다.
•
무결성을 가장 엄격하게 보장하는 방식이다.
무결성 제약 조건은 단순한 데이터의 규칙이 아니다. 사용자에게 신뢰할 수 있는 데이터를 제공하고, 시스템의 일관성과 안정성을 보장하는 핵심 원칙이다. 잘못된 참조나 삭제로 인한 데이터 오류는 곧 사용자 경험의 저하로 이어질 수 있다. 따라서, 실제로 테이블을 설계를 할 때는 각 상황에 맞는 삭제/수정 전략을 신중히 선택하는 것이 중요하다.
