[DB] SQL DDL 핵심: CREATE TABLE, 데이터 타입, 제약조건

 

 

이번 포스팅에서는 SQL의 기본 구조 중에서도 데이터 정의(Data Definition) 와 데이터 타입(Data Types), 제약조건 (Constraints)을 중심으로 다뤄보겠다. 조회(SELECT) 문 이전까지의 핵심부분인 SQL이 어떻게 스키마를 정의하고, 테이블을 만들고, 각 속성(attribute)의 타입을 다루는지를 알아보자.

 

 

SQL의 배경 

SQL(Structured Query Language)은 단순히 쿼리 언어가 아니라, 관계형 데이터베이스를 조작하기 위한 표준 언어이다. 그 뿌리는 tuple calculus (튜플 해석) 에서 시작되었으며, 초기에는 SQUARE라는 언어로 제안되었다.
이후 IBM에서 SYSTEM R 프로젝트를 위해 개발하며, SEQUEL (Structured English QUEry Language) 이라는 이름을 사용했는데, 저작권 문제로 지금의 "SQL"로 변경되었다.

 

현재 SQL은 모든 RDBMS의 표준 언어로 자리 잡았다.
(MySQL, Oracle, PostgreSQL, SQL Server 등 모든 상용/오픈소스 DB의 공통 언어)

 

 

---

 

SQL에서의 스키마와 카탈로그 (Schema and Catalog)

SQL의 데이터 정의 명령(DDL, Data Definition Language)은 "무엇을 만들고 정의할지"를 담당한다.

Schema (스키마)

• SQL92(SQL2) 이후 도입된 개념으로, 같은 데이터베이스 내의 객체(테이블, 뷰, 도메인 등) 를 묶는 논리적 단위.
• CREATE SCHEMA 문으로 생성한다.
  • CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith';
• 스키마에는 두 가지 핵심 구성요소가 있다.
  1. Authorization identifier: 스키마를 소유하는 사용자나 계정 (ex. 'Joshua')
  2. Descriptors: 스키마 안에 포함된 객체들의 정의문 (ex. CREATE TABLE ...)

 

실제로 데이터베이스는 아래와 같은 구조로 되어있다. 대부분의 DBMS에서는 이미 스키마 개념이 자동으로 설정되어 있어서, 직접 CREATE SCHEMA를 쓰는 일이 거의 없다.

Database
 ├── Schema: COMPANY
 │     ├── Table: EMPLOYEE
 │     ├── Table: DEPARTMENT
 │     └── View: EMP_DEPT_VIEW
 └── Schema: HR
       ├── Table: USERS
       ├── Table: SALARY

 

 

Catalog (카탈로그)

• 여러 스키마를 모은 논리적 그룹
• 모든 SQL 환경에는 기본적으로 INFORMATION_SCHEMA 라는 특수 스키마가 존재한다. (DB 내 메타데이터, 즉 "테이블 목록, 컬럼 정보" 등을 담고 있다.)

 

---

 

 

CREATE TABLE — 테이블 정의하기

SQL에서 새로운 릴레이션(테이블)을 정의할 때 사용하는 명령이다.

CREATE TABLE EMPLOYEE (
  Fname VARCHAR(15),
  Lname VARCHAR(15),
  Ssn CHAR(9),
  ...
);

• 테이블명 (R) 을 지정하고,
• 속성(Attributes) 과 그들의 데이터 타입,
• 그리고 초기 제약조건(Constraints) 을 함께 정의한다.
• 스키마 이름을 명시할 수도 있다.
  • CREATE TABLE COMPANY.EMPLOYEE ... (명시적)
  • CREATE TABLE EMPLOYEE ... (암시적)

 

Base tables은 실제로 파일 형태로 저장되는 반면, Views(뷰) 는 물리적 파일이 없는 가상의 테이블이다.

 

 

---

 

 

SQL의 데이터 타입 정리 (Attribute Data Types & Domains)

SQL은 데이터 타입이 꽤 다양하다. 대표적인 타입들을 실제 예시와 함께 정리하면 다음과 같다.

1. Numeric Types

타입	설명	예시
INTEGER, INT, SMALLINT	정수 타입	100
FLOAT, REAL, DOUBLE PRECISION	실수 타입	3.14
DECIMAL(i, j) / NUMERIC(i, j)	고정 소수점	DECIMAL(5,2) → 123.45

• i: 전체 자릿수 (precision)
• j: 소수점 이하 자릿수 (scale)
• scale의 기본값은 0

 

2. Character String Types

타입	설명	예시
CHAR(n), CHARACTER(n)	고정 길이 문자열	'A' → 'A '
VARCHAR(n)	가변 길이 문자열	'Hello'
CLOB(n)	대용량 텍스트 (Character Large Object)	CLOB(20M)

CHAR는 길이가 맞지 않으면 오른쪽에 공백이 자동으로 추가된다.

 

 

3. Bit String Types

타입	설명	예시
BIT(n)	고정 길이 비트열	B'10101'
BIT VARYING(n)	가변 길이 비트열	B'110'
BLOB(n)	대용량 바이너리 (Binary Large Object)	BLOB(20M)

 

 

4. Date & Time Types

타입	설명	예시
DATE	DATE '2014-09-27'	연-월-일 (YYYY-MM-DD)
TIME	TIME '09:12:47'	시:분:초 (HH:MM:SS)
TIMESTAMP	TIMESTAMP '2014-09-27 09:12:47.648302'	날짜+시간+소수초
INTERVAL	'1 DAY'	날짜나 시간 간격 표현

날짜/시간 비교 연산 가능: '2018-10-02' < start_date

 

5. Boolean Type

• TRUE, FALSE, NULL 중 하나의 값만 가질 수 있다.

 

6. Domain

• 자주 쓰이는 타입을 별도로 정의해 이름 붙일 수 있다.
• 일종의 사용자 정의 타입처럼 활용 가능.
• 유지보수성과 가독성이 높아진다.

CREATE DOMAIN SSN_TYPE AS CHAR(9);

 

→ 이후 여러 테이블에서 SSN_TYPE 으로 재사용 가능

 

 

7. User-Defined Type (UDT)

• PostgreSQL 등에서는 CREATE TYPE 명령으로 객체지향적 데이터 타입 정의도 가능하다.

 

 

---

 

 

COMPANY 스키마 예시

 

 

 

CREATE TABLE 예시

COMPANY 스키마의 각 테이블은 다음과 같은 방식으로 정의된다.

CREATE TABLE DEPARTMENT (
  Dname VARCHAR(15) NOT NULL,
  Dnumber INT NOT NULL,
  Mgr_ssn CHAR(9),
  Mgr_start_date DATE,
  PRIMARY KEY (Dnumber),
  UNIQUE (Dname)
);

이후 EMPLOYEE, PROJECT, WORKS_ON, DEPENDENT 테이블들이 정의된다.

 

단, 이 과정에서 외래키(Foreign Key) 가 순환참조(Circular Reference)를 일으킬 수 있다.
예를 들어, EMPLOYEE가 DEPARTMENT를 참조하고, DEPARTMENT가 다시 EMPLOYEE의 Mgr_ssn을 참조할 때 발생한다.

-> 이런 경우 ALTER TABLE 문으로 나중에 제약조건을 추가하는 식으로 해결한다.

 

 

---

 

 

그러면 이제 데이터 제약조건(Constraints) 을 다뤄보자.
데이터 무결성(integrity)을 지키는 핵심 개념으로, 실제 테이블 설계의 품질을 좌우한다.

 

1. SQL의 핵심 제약조건 (Specifying Constraints in SQL)

SQL에서는 데이터의 신뢰성과 일관성을 유지하기 위해 다양한 제약조건(Constraints) 을 제공한다. 이 제약조건들은 보통 CREATE TABLE 문 내부에서 정의된다.

 

대표적인 기본 제약조건은 다음 세 가지다:

1. Key Constraint
   → 속성이 고유(Unique)해야 한다는 제약.
   예: 한 사람의 주민번호는 유일해야 함.
2. Entity Integrity Constraint
   → 기본키(Primary Key)는 NULL이 될 수 없다.
   (엔티티 식별 불가능 방지)
3. Referential Integrity Constraint
   → 외래키(Foreign Key)는 다른 테이블의 기본키를 참조해야 한다.
   만약 참조 대상이 존재하지 않으면 오류 발생.
   단, NULL 값은 허용될 수 있다.

 

 

2. 속성 제약조건 (Attribute Constraints)

DEFAULT

특정 속성의 기본값을 지정할 때 사용한다. 필수값을 생략해도 default가 있으면 default 값이 들어감

Salary DECIMAL(10,2) DEFAULT 3000.00

 

NOT NULL

해당 컬럼이 반드시 값을 가져야 함을 명시한다. 

Fname VARCHAR(15) NOT NULL

 

CHECK

속성 값이 특정 조건을 만족해야 할 때 사용한다.

Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 10)
Age INT NOT NULL CHECK (Age >= 15 AND Age < 65)

 

이런 CHECK 절은 튜플이 추가되거나 수정될 때마다 자동으로 검증된다.

 

 

3. 키 제약조건 (Key Constraints)

PRIMARY KEY

테이블의 기본키(Primary Key) 를 지정한다.
기본키는 테이블 내에서 각 튜플을 유일하게 식별해야 하며, NULL을 허용하지 않는다. 한개 또는 그 이상의 attribute가 PK가 될 수 있다. 

Dnumber INT PRIMARY KEY

 

또는 여러 컬럼을 묶어서 복합키(Composite Key)로도 지정할 수 있다.

PRIMARY KEY (State, Number)

 

UNIQUE

 

기본키는 아니지만, 중복을 허용하지 않아야 하는 컬럼에 사용한다.

Dname VARCHAR(15) UNIQUE

 

또는 여러 속성의 조합에 대해 유일성을 보장할 수도 있다.

UNIQUE (State, Number)

 

• PRIMARY KEY: 유일성 + NULL 불허
• UNIQUE: 유일성만 보장 (NULL 허용 가능)

 

4. 외래키 제약조건 (Referential Integrity Constraints)

FOREIGN KEY

외래키는 다른 테이블의 기본키를 참조한다. 이 제약을 통해 데이터 간의 관계를 유지할 수 있다.

FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)

 

만약 참조 대상이 존재하지 않거나, 삭제/수정될 때 행동을 제어하고 싶다면 참조 트리거 액션(Referential Triggered Action) 을 추가할 수 있다. 기본적으로 외래키 위반이 발생하면 DBMS는 해당 변경(INSERT/UPDATE/DELETE)을 거부(reject)한다. 하지만 ON DELETE, ON UPDATE 절을 사용하면 위반 상황에서 자동으로 값을 변경하거나 삭제하도록 동작을 지정할 수 있다.

 

 

참조 트리거 액션 (ON DELETE / ON UPDATE)

SET NULL	참조 중인 레코드가 삭제되면, 해당 외래키를 NULL로 설정
CASCADE	참조 중인 레코드가 삭제되면, 연결된 레코드도 함께 삭제
SET DEFAULT	기본값으로 변경 (Oracle은 지원하지 않음)

FOREIGN KEY (Mgr_ssn)
  REFERENCES EMPLOYEE(Ssn)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

 

이처럼 데이터 일관성을 자동으로 유지할 수 있다. DBMS는 위와 같은 트리거 동작을 참조 무결성 위반 시 자동으로 수행한다.

 

 

 

5. 제약조건 이름 지정 (Naming Constraints)

여러 제약조건을 두다 보면, 나중에 ALTER TABLE 등으로 수정해야 할 일이 생긴다. 그럴 때를 대비해 제약조건에 이름을 붙일 수 있다.

CREATE TABLE Vehicle (
  State CHAR(2),
  Number INT,
  Owner VARCHAR(30),

  -- 테이블 전체 제약조건
  CONSTRAINT reg_no_u UNIQUE (State, Number)
);

 

여기서 reg_no_u는 제약조건(constraint)의 이름이다.

 

이후 ALTER TABLE 명령에서 CONSTRAINT reg_no_u를 지정해 쉽게 변경 가능하다. 

ALTER TABLE Vehicle
ADD CONSTRAINT reg_no_u UNIQUE (State, Number);

 

 

 

6. 튜플 단위 제약조건 (Tuple Constraints using CHECK)

CHECK는 속성 단위뿐 아니라 튜플 단위(행 단위) 로도 사용할 수 있다. 이 경우 CREATE TABLE 문 끝부분에 작성한다.

CREATE TABLE EMPLOYEE (
  Fname VARCHAR(15),
  Lname VARCHAR(15),
  Salary DECIMAL(10,2),
  Dno INT,
  CHECK (Salary > 0 AND Dno > 0)
);

• 모든 튜플(행)에 대해 조건이 개별적으로 평가된다.
• 새로운 튜플이 INSERT되거나, 기존 튜플이 UPDATE될 때마다 확인된다.

 

 

COMPANY 스키마의 제약조건 예시

 

 

 

 

 

 

 

출처: 경북대학교 이천희 교수님, “데이터베이스” 강의 자료