[DB설계] 14. '열(컬럼)'의 설정

표준 SQL 및 데이터베이스 입문

Mar 1. 2025

다시한번, 테이블 정의에 대해 무엇을 할 수 있는지 확인해 봅시다. 우선 열(column)부터 알아봅시다.

우선 확실히 기억해야 할 것은 관계형 데이터베이스에서는 각 열에는 값이 하나씩만 들어가는 것이 기본 원칙이라는 것입니다. 예를 들어, 학생번호 열에는 학생번호가 하나만 들어갑니다.

학생번호는 하나만 있기 때문에 특별히 문제가 없을까?

물론 문제가 될 수 있습니다. 예를 들어, 전화번호의 경우를 생각해 봅시다. 독자는 전화번호를 몇 개 가지고 있습니까? 집전화, 휴대전화 등이 있습니다. 휴대전화의 전화번호부에서는 한사람당 여러개의 전화번호를 등록할 수 있도록 되어 있습니다.

관계형 데이터베이스의 열(column, 컬럼)에는 한개의 데이터만 등록됩니다. 즉, 학생번호 열에는 학생번호 하나만, 전화번호 열에는 전화번호 하나만 등록하는 것입니다. 이러한 데이터를 원자적(atomic) 데이터라고 부르기도 합니다. 원자적이란 더 이상 분할할 수 없다는 것을 의미합니다. 또한, 하나의 열에는 동일한 종류의 데이터만 등록합니다.

엑셀등의 스프레드시트 소프트웨어의 셀처럼, 때로는 명칭, 때로는 금액, 때로는 소계와 같이 서로 다른 데이터를 혼합하여 입력하지 않습니다. (데이터형의 정의에 따라 입력하면 오류가 발생할 수도 있습니다)

원자적 데이터를 입력하기

⑴ 데이터의 '형(type)'이란?

열에는 원자적인 데이터를 입력합니다. 그리고 데이터에는 '형(type)'이라는 것이 있습니다. 데이터형이란, 문자열이나 숫자와 같이 데이터의 종류를 말하는 것입니다. CREATE TABLE에서 CHAR이나 INTEGER등으로 지정했던 것을 말하는 것입니다. 대략적으로는 문자열, 숫자, 날짜시간의 3종류입니다. 엑셀등에서도 숫자는 사칙연산이 가능하듯이, 데이터형에 따라 처리할 수 있는 내용이 달라집니다.

관계형 데이터베이스에서는 각 열에 대해 '이 열은 문자열', '이 열은 숫자'라고 미리 결정해 둡니다. SQL에서는 테이블을 생성할 때 데이터형을 지정함으로써, 등록할 수 있는 데이터 종류를 제한합니다. 관계형 데이터베이스에서 다루는 데이터형은 크게 문자열형, 숫자형, 날짜시간형의 3종류로 나뉩니다.

문자열형

문자수가 정해진 고정길이형(CHAR)과 가변길이형(VARCHAR)이 있습니다. 또한 많은 DBMS에서는 긴 문장을 저장하기 위한 TEXT형도 사용됩니다. 보통 문자열형에서는 사용할 문자코드(문자집합)나 정렬순서를지정할 수 있습니다.

숫자형

정수형과 실수형이 있습니다. 정수는 주로 INTEGER가 사용되며, 실수는 주로 NUMBERIC과 FLOAT가 사용됩니다. INTEGER는 전체 자릿수를, NUMBERIC과 FLOAT는 유효자릿수와 소수자릿수를 설정할 수 있습니다. NUMBERIC과 FLOAT의 차이점은 정밀도와 처리속도에 있으며, 금액처럼 속도를 희생하더라도 소수점 이하까지 정확한 값이 필요할 경우, NUMBERIC, 그 외에는 빠른 처리가 가능한 FLOAT를 사용합니다.

날짜시간형

날짜형(DATE), 시간형(TIME), 날짜시간형(TIMESTAMP)외에도, 일수나 시간을 관리하는 기간형(INTERVAL)등이 있습니다.

⑵ 열의 초기화 (DEFAULT절)

초기값을 설정할 수 있을까요?

예를 들어, 일단 '0'으로 하거나 현재 날짜와 시간을 입력하는등이 가능합니다. 이때는 DEFAULT절을 사용하면 됩니다. 데이터 추가시 값이 지정되지 않은 경우, 자동으로 들어가는 값을 초기값(default value)이라고 부르며, 열의 초기값은 데이터형 뒤에 'DEFAULT 값'으로 지정합니다. 다음은 '재고마스터'라는 테이블을 생성하는 CREATE문입니다.

상품코드는 5자리이며, 이 값이 기본키로 지정되어 있습니다. 그리고, 상품명, 재고수, 등록일자라는 열이 있으며, 재고수의 초기값은 '0', 등록일자의 초기값은 'CURRENT_TIMESTAMP'로 지정되어 있습니다. CURRENT_TIMESTAMP는 표준 SQL에 규정된 값으로 현재 날짜와 시간을 의미합니다. 즉, INSERT를 실행한 시각이 저장됩니다.

테이블명과 열명은 자유롭게 정할 수 있습니다. 아래 예제에서는 정의 취지를 쉽게 전달하기 위해 한국어 테이블명과 열명을 사용하고 있지만, 원칙적으로는 영문과 숫자만 사용하는 것이 좋습니다. 환경에 맞추어 입력하기 쉬운 이름을 사용합니다. 여러 설정값으로 시험해보고 싶다면, 별도의 테이블명을 사용하거나 'DROP TABLE 테이블명;'으로 기존 테이블을 삭제한 후, 다시 CREATE문을 실행하면 됩니다.

다음 INSERT문에서는 '재고마스터' 테이블에 상품코드 'A0001'과 상품명 '연필'이라는 데이터를 등록합니다. 재고수와 등록일자에 대해서는 값을 지정하지 않았으므로, 초기값인 '0'과 'INSERT'를 실행한 시각이 자동으로 입력됩니다. 어떤 값이 입력되었는지는 SELECT문으로 확인할 수 있습니다.

⑶ 등록가능한 값의 제한 (CHECK 제약)

시험점수는 단순히 정수여야 할 뿐만 아니라, 0에서 100사이의 정수여야 한다고 제한하고 싶은 가능할까?

CHECK제약을 사용하면 가능합니다. 예를 들어, 숫자범위제한이나, 시험과목의 경우, 국어, 수학, 영어 중 하나여야 한다고 지정할 수 있습니다.

문자열도 적용할 수 있으며 변경가능성 여부나 추가로 관리하고 싶은 정보가 있는지에 따라 달려 있습니다. 단순히 선택 가능한 값만 제한하고 싶다면 CHECK제약을 사용하지만, 만약, 변경될 가능성이 있거나 관리해야 할 추가정보가 있다면 별도의 마스터 테이블을 만드는 것이 좋습니다.

'캠퍼스'는 지금은 단순히 '강남구', '서초구', '송파구', '종로구' 4가지 선택지만 있지만, 앞으로 더 늘어날 예정이고 언젠가는 강사정보도 관리해야 할 경우를 대비하여 '캠퍼스마스터'가 있는 것도 좋을 것입니다. 참고로 표준 SQL에서는 CHECK제약을 사용하여 상당히 복잡한 조건도 지정할 수 있도록 되어 있지만, 실제로 어느 정도까지 지원하는지는 DBMS에 따라 달라집니다. 여기에서는 간단하게 값의 범위만 설정해 봅니다.

테이블에서 열을 정의할 때, 'CHECK()'을 사용하여 저장할 수 있는 값을 제한할 수 있습니다. 이를 'CHECK제약(check constraint)'이라고 합니다.

다음은 앞서 사용했던 exams 테이블의 정의 예제입니다. 여기서는 'subject의 값은 국어, 수학, 영어 중 하나이어야 힘'와 'score는 0이상 100 이하'로 지정하고 있습니다. CHECK 제약에서는 괄호안의 식이 'FALSE(거짓)'로 평가되면 오류가 발생하며, 해당 데이터의 추가나 변경이 불가능해집니다. 예를 들어,

"(subject = '국어' OR subject = '수학' OR subject = '영어')"의 경우, subject가 국어, 수학, 영어 중 어느 것과도 일치하지 않으면 FALSE가 되어 등록할 수 없습니다. 또한, 이 부분은 IN을 사용하여, "CHECK(subject IN ('국어', '수학', '영어'))"와 같이 작성할 수 있습니다. 그리고 score에 대한 CHECK제약은 BETWEEN을 사용하여, "CHECK(score BETWEEN 0 AND 100)"와 같이 지정할 수도 있습니다.

PRIMARY KEY등과 마찬가지로, 열의 정의와 별도로 선언할수도 있습니다. 여러 열의 값을 참좋해야 하는 경우나 여러 CHECK제약을 한데 묶어두거나, CHECK 제약에 이름을 붙이고 싶은 경우 등에는 별도로 작성하는 것이 더 이해하기 쉽습니다.

제약이름은 'CONSTRAINT 제약명'으로 지정합니다. 제약명은 ALTER TABLE문으로 정의를 변경할 때 사용됩니다. 또한, 제약위반시 DBMS가 반환하는 오류메시지에도 사용됩니다. 다음은 수하물접수를 기록하는 테이블의 정의 예제입니다. 주키는 '접수번호'이며, '접수일자'에는 현재 날짜와 시간이 자동으로 등록됩니다. 사이즈로는 'W', 'D', 'H' 입력이 필요하지만, 각각의 값은 10을 초과해야 하며, 또한 합계가 80이상, 180이하라는 제약을 설정하려고 합니다. 합계 크기에 대해서는 '최소크기'와 '최대크기'라는 2개의 제약으로 나누었습니다.

PostgreSQL의 경우, 열 정의에 CHECK제약을 추가할 때도 'CONSTRAINT 제약명'을 지정할 수 있습니다. '열명 데이터형 NOT NULL CONSTRAINT 제약명 CHECK(조건식)'와 같이 작성합니다.

⑷ 문자열/숫자/날짜 및 시간 외의 데이터형

SQL에서 기본적으로 다루는 것은 문자열, 숫자, 날짜 및 시간과 같이 단순한 값들이지만, 데이터베이스에서 관리하고자 하는 데이터는 그것만으로는 부족합니다. 예를 들어, 이미지와 같이 용량이 큰 데이터도 있습니다. 상품사진처럼 함계 괸라하고 싶은 경우가 있습니다. 또한 필기시험내용을 스캔한 데이터를 관리하는 강사도 있을 수 있습니다.

관계형 데이터베이스의 기반이 되는 관계형 모델에서는 숫자나 문자ㅏ로 표현할 수 있는 데이터만 다루며, 관계형 데이터베이스가 만들어져 실제로 사용되기 시작한 1970년대 컴퓨터의 처리능력을 고려해도 이미지 같은 데이터를 관리하도록 설계되지는 않았습니다. 하지만 관계형 데이터베이스는 매우 편리하기 때문에 이미지도 같이 관리하고 싶은 요구가 당연히 있습니다. 같이 관리할 수 있으면 정말 편리하기 때문입니다.

그래서 SQL:1999에서는 이러한 데이터를 다루기 위한 BLOB타입등이 새롭게 도입되었고, JSON이나 XML등을 다루는 방법도 규정되어 있습니다. 각 DBMS에서는 독자적인 확장도 이루어지고 있습니다. 다만, 이미지나 음성같은 데이터는 용량이 크고, 문자나 숫자와 달리 관계형 데이터베이스에서는 구조가 정의되지 않은 비구조화 데이터(unstructured data)이기 때문에 검색성능이 떨어질 수 있습니다. 따라서, DBMS와는 별도로 관리하여 DBMS에서는 보관위치나 파일명등을 관리하는 방법도 충분히 검토해 볼 필요가 있습니다.

표준SQL에서는 이미지와 같은 대용량 데이터를 저장하기 위해 BLOB (Binary Large Object)형이나 ARRAY(배열)형, 나아가 JSON(JavaScript Object Notation)이나 XML(Extensible Markup Language)등을 다룰 수 있도록 규정하고 있습니다. 하지만, DBMS에 따라서는 표준화 이전부터 이러한 데이터를 독자적인 방식으로 지원하거나, 전혀 지원하지 않는 경우도 있으므로, 사용중인 제품에 따라 다르게 취급합니다. 자신이 사용하는 DBMS에서 어떤 방식으로 지원하는지, 관리용 도구가 있는지 확인후 신중하게 검토후 사용하기 바랍니다.

⑸ 도메인(정의역)이란?

데이터형에 대해 실제로 사용하는 것은 아니지만, 잠시나마 기억해두면 좋을 것이 있습니다. 그것은 바로 도메인입니다. 도메인은 인터넷에서 사용하는 WWW(월드와이드웹)이나 이메일주소와 같은 것을 말하는 것 같지만, 여기서 말하는 도메인은 데이터의 집합을 의미합니다. 예를 들어, '인명' 또는 구체적으로 '학생이름'과 같이, 관계형 데이터베이스의 열(컬럼)에는 같은 도메인에 속하는 값만 들어가야 한다고 생각하는 것입니다. 즉, 인명 컬럼에는 사람 이름만 들어가야 한다는 의미라는 것입니다.

동일한 성질을 가진 값들의 집합을 도메인(domain) 또는 정의역이라고 합니다. 예를 들어, '학생마스터' 테이블의 '이름'열의 도메인은 사람의 이름들의 집합입니다.

관계형 데이터베이스의 기초가 되는 관계모델(relational model)에서는 서로 다른 도메인 간의 값 비교를 금지하고 있습니다. 예를 들어, 키와 몸무게는 둘다 숫자형으로 표현할 수 있지만, 둘을 비교하는 것은 의미가 없습니다. 이 두 값의 도메인이 다르기 때문입니다. 그러나, 관계형 데이터베이스에서는 여기까지 엄격하게 제한하지 않습니다. 숫자끼리나 문자열끼맃처럼 호환가능한 형끼리는 비교나 연산을 허용합니다.

표준 SQL에서는 도메인을 CREATE DOMAIN으로 정의하도록 규정하고 있으며, Oracle이나 PostgreSQL에서는 CREATE DOMAIN을 사용할 수 있습니다. 반면에 MySQL과 MariaDB는 2024년말까지 지원하지 않았습니다. 참고로 PostgreSQL에서 CREATE DOMAIN을 사용한 정의 예제를 보며주며, CREATE TYPE으로 데이터형을 정의하는 것도 가능합니다.

도메인을 어떻게 다루면 좋을까요?

실제로 데이터베이스에서 하고자 하는 일은 '입력가능한 값의 범위를 지정하는' 것이 대부분이 아닐까요? 그렇다면 CHECK제약을 사용하면 되고, MariaDB는 ENUM형(열거형)을 사용하여 '열명 ENUM(값1, 값2, ...)'와 같이 입력할 수 있는 값을 미리 정해둘수도 있습니다.