logo

[SQLite] 테이블 생성

 

CREATE TABLE

SQLite에서 CREATE TABLE 문은 새로운 테이블을 생성하기 위해 사용됩니다. 이 구문은 테이블에 포함될 열(컬럼)과 그 데이터 타입, 그리고 필요한 경우 다양한 제약 조건(예: 기본 키, 외래 키, 고유 제약 조건 등)을 정의하는 데 사용됩니다.

다음은 SQLite에서 CREATE TABLE 문을 사용한 간단한 예제입니다:

CREATE TABLE Students (
    StudentID INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Major TEXT,
    Year INTEGER,
    GPA REAL
);

이 예제에서는 Students라는 이름의 테이블을 생성하고 있습니다. 테이블에는 다음과 같은 열이 포함됩니다:

  • StudentID: 학생의 고유 번호로, INTEGER 데이터 타입을 가지며 PRIMARY KEY로 지정되어 자동으로 고유 값이 할당됩니다.
  • Name: 학생의 이름을 나타내며, TEXT 데이터 타입을 가지고 NOT NULL 제약 조건이 적용되어 반드시 값이 있어야 합니다.
  • Major: 학생의 전공을 나타내며, TEXT 데이터 타입을 가집니다. 이 열에는 제약 조건이 명시되지 않았으므로, NULL 값을 가질 수 있습니다.
  • Year: 학생이 몇 학년인지를 나타내며, INTEGER 데이터 타입을 가집니다.
  • GPA: 학생의 평균 학점을 나타내며, REAL 데이터 타입을 사용해 소수점을 포함한 값을 저장할 수 있습니다.
 

IF NOT EXISTS

IF NOT EXISTS 구문은 SQLite에서 테이블을 생성하기 전에 동일한 이름의 테이블이 데이터베이스에 존재하는지 여부를 검사하는데 사용됩니다. 이 구문을 CREATE TABLE 명령어와 함께 사용하면, 해당 이름을 가진 테이블이 이미 데이터베이스에 존재하지 않을 경우에만 새로운 테이블이 생성됩니다. 만약 테이블이 이미 존재한다면, CREATE TABLE 명령은 무시되고 어떠한 오류도 발생하지 않습니다. 이는 스크립트나 애플리케이션에서 데이터베이스를 초기화하거나 업데이트할 때 중복 생성으로 인한 오류를 방지하고자 할 때 유용합니다. 예를 들어, CREATE TABLE IF NOT EXISTS Students (...); 구문은 Students 테이블이 이미 존재하지 않을 때만 테이블을 생성합니다.

 

AS SELECT

CREATE TABLE ... AS SELECT 구문은 SQLite에서 기존 테이블의 선택된 데이터를 기반으로 새 테이블을 생성하는 데 사용됩니다. 이 명령은 특히, 특정 쿼리의 결과를 새로운 테이블로 저장하고자 할 때 유용합니다. 이 방식을 사용하면, 기존 데이터를 필터링, 정렬, 집계하는 등의 변환 작업 후 그 결과를 직접 새 테이블로 만들 수 있습니다. 이 명령은 데이터 분석, 보고, 혹은 임시 데이터 집합의 생성과 같은 작업을 수행할 때 특히 유용합니다.

고객 데이터베이스에서 특정 조건을 만족하는 고객들만을 추출하여 새로운 테이블을 생성하는 예시를 들어보겠습니다.

기존 Customers 테이블에서 Year가 2020 이상이고 Status가 'Active'인 고객들만을 선택하여, 이 데이터를 바탕으로 ActiveCustomers2020라는 새 테이블을 생성하고자 한다고 가정해봅시다.

CREATE TABLE ActiveCustomers2020 AS
SELECT * FROM Customers
WHERE Year >= 2020 AND Status = 'Active';

위 SQL 명령은 Customers 테이블에서 조건을 만족하는 모든 행을 선택하고, 이를 ActiveCustomers2020라는 이름의 새로운 테이블로 저장합니다. 생성된 새 테이블은 기존 테이블의 구조(열의 이름과 데이터 타입)를 유지하지만, CREATE TABLE ... AS SELECT 구문으로 생성된 테이블에는 기본 키나 인덱스 같은 제약 조건이 자동으로 포함되지 않는 점에 주의해야 합니다. 필요한 경우, 추가적인 SQL 명령을 사용하여 이러한 제약 조건을 수동으로 추가할 수 있습니다.

 

기본 키

SQLite에서 기본 키(PRIMARY KEY)는 테이블 내 각 행을 유일하게 식별하는 역할을 합니다. 각 테이블은 최대 하나의 기본 키를 가질 수 있으며, 이는 하나의 열이나 여러 열의 조합으로 구성될 수 있습니다. 기본 키로 지정된 열은 테이블 내에서 고유한 값을 가져야 하며, NULL 값을 허용하지 않습니다(일부 예외를 제외).

SQLite는 기본적으로 대부분의 상황에서 PRIMARY KEY 열에 NULL 값을 허용할 수 있지만, 이는 SQL 표준과 다르며 특정 조건(예: 열이 INTEGER PRIMARY KEY이거나 테이블이 WITHOUT ROWID 테이블인 경우)에서만 적용됩니다. 이러한 유연성은 레거시 애플리케이션의 호환성을 위해 유지되고 있지만, 데이터 무결성을 강화하기 위해서는 NOT NULL 제약 조건을 명시적으로 추가하는 것이 좋습니다.

 

단일 열 기본키

열 정의에 PRIMARY KEY 키워드를 추가하여 단일 열을 기본 키로 지정할 수 있습니다. 예를 들어, StudentID INTEGER PRIMARY KEY는 StudentID 열을 기본 키로 지정합니다.

다음 SQL 문은 Students 테이블을 생성합니다. 여기서 StudentID 열이 단일 열 기본키로 지정되어 있으며, 각 학생에 대해 고유한 식별자 역할을 합니다.

CREATE TABLE Students (
    StudentID INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Major TEXT,
    Year INTEGER,
    GPA REAL
);

이 예시에서 StudentID 열은 INTEGER PRIMARY KEY로 선언되어 있어, SQLite가 자동으로 고유한 값을 할당하는 자동 증가 기능을 가지게 됩니다. 학생의 이름(Name), 전공(Major), 학년(Year), 평균 학점(GPA) 등의 추가 정보도 저장됩니다.

 

INTEGER PRIMARY KEY

단일 열 기본 키가 있고 해당 열의 데이터 타입이 INTEGER인 경우, SQLite는 이를 INTEGER PRIMARY KEY로 취급합니다. 이는 테이블의 rowid와 동일하게 취급되어 자동으로 증가하는 고유값을 할당받을 수 있습니다.

WITHOUT ROWID 테이블에서는 기본 키가 필수이며, 이 경우 테이블에 rowid가 없습니다. 기본 키는 테이블 내에서 행을 유일하게 식별하는 데 사용됩니다.

WITHOUT ROWID 테이블은 SQLite에서 테이블을 생성할 때 WITHOUT ROWID 옵션을 사용하여 생성된 테이블입니다. 이러한 테이블은 기본 키가 명시적으로 정의되어야 하며, SQLite의 내부 rowid 메커니즘을 사용하지 않습니다. 이는 테이블의 크기와 삽입 속도를 최적화할 수 있는 경우에 유용할 수 있습니다. WITHOUT ROWID 테이블에서는 기본 키를 사용하여 데이터를 직접 저장하고 접근합니다.

다음은 WITHOUT ROWID를 사용하여 Books 테이블을 생성하는 예시입니다. 이 테이블은 각 책에 대한 정보를 저장하며, BookID와 ISBN의 조합을 복합 기본 키로 사용합니다.

CREATE TABLE Books (
    BookID INTEGER,
    ISBN TEXT,
    Title TEXT NOT NULL,
    Author TEXT,
    PublishedYear INTEGER,
    PRIMARY KEY (BookID, ISBN)
) WITHOUT ROWID;

이 예시에서 Books 테이블은 BookID와 ISBN 열의 조합으로 구성된 복합 기본 키를 가지며, WITHOUT ROWID 옵션으로 생성됩니다. 이는 BookID와 ISBN의 조합이 테이블 내에서 각 책을 유일하게 식별하는 데 사용됨을 의미합니다. Title, Author, PublishedYear 열은 각 책의 제목, 저자, 출판 연도 정보를 저장합니다.

WITHOUT ROWID 테이블을 사용하는 주된 이유는 성능 최적화와 공간 효율성입니다. 테이블의 기본 키를 직접 인덱스로 사용함으로써 데이터베이스가 데이터를 더 효율적으로 관리할 수 있게 됩니다. 그러나 모든 경우에 WITHOUT ROWID 테이블이 유용한 것은 아니며, 사용하기 전에 테이블의 사용 사례와 요구 사항을 고려해야 합니다.

 

복합 기본키

여러 열을 포함하는 기본 키를 생성하려면, CREATE TABLE 문의 PRIMARY KEY 절에 열 이름의 목록을 지정합니다. 예: PRIMARY KEY(column1, column2).

다음 SQL 문은 CourseRegistrations 테이블을 생성합니다. 여기서 StudentID와 CourseID 열의 조합이 복합 기본키로 지정되어 있어, 각 등록에 대해 고유한 조합을 보장합니다.

CREATE TABLE CourseRegistrations (
    StudentID INTEGER,
    CourseID INTEGER,
    RegistrationDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

이 예시에서 StudentID와 CourseID의 조합은 테이블 내에서 각각의 등록을 고유하게 식별하기 위해 사용됩니다. 같은 학생이 동일한 과정에 두 번 등록할 수 없음을 보장합니다. RegistrationDate 열은 등록한 날짜를 저장합니다.

복합 기본키를 사용하는 경우, 지정된 열의 조합이 테이블 내의 모든 행에 대해 유일해야 합니다. 이를 통해 데이터의 무결성을 유지할 수 있습니다.

 

열 정의

SQLite에서 DEFAULT, COLLATE, GENERATED ALWAYS AS, UNIQUE, CHECK는 테이블을 정의할 때 열에 적용할 수 있는 다양한 옵션 및 제약 조건입니다. 각각의 기능과 예시는 다음과 같습니다:

 

DEFAULT

DEFAULT는 열에 대한 기본값을 지정합니다. 새로운 행이 추가될 때 해당 열에 대한 값이 명시적으로 제공되지 않으면, SQLite는 DEFAULT로 지정된 값을 사용합니다.

CREATE TABLE Employees (
    EmployeeID INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    StartDate DATE DEFAULT CURRENT_DATE
);

StartDate 열에 대해 기본값으로 현재 날짜(CURRENT_DATE)를 지정합니다.

 

COLLATE

COLLATE는 열에 대한 문자열 비교 시 사용할 정렬 순서(콜레이션)를 지정합니다.

COLLATE 뒤에 오는 콜레이션 이름에는 NOCASE, BINARY와 RTRIM가 있습니다.

  • BINARY 기본 콜레이션 방식으로, 바이트 값의 순서대로 문자열을 비교합니다. 이 방식은 대소문자를 구분하며, ASCII 코드 값에 기반한 정확한 바이트 비교를 수행합니다. BINARY 콜레이션을 사용하면, 같은 문자라도 대문자와 소문자가 다르게 취급되어 'A'는 'a'보다 작다고 간주됩니다. 이는 가장 엄격한 비교 방식을 제공하며, 데이터의 정렬 순서나 비교 결과가 해당 시스템의 바이트 순서에 의존적입니다.
  • NOCASE: 대소문자를 구분하지 않습니다.
  • RTRIM 콜레이션은 문자열의 끝에 있는 공백을 비교에서 제외하고 나머지 부분을 BINARY 콜레이션과 동일한 방식으로 비교합니다. 이는 문자열의 오른쪽 끝에 있는 하나 이상의 공백 문자가 비교 결과에 영향을 주지 않도록 합니다. 예를 들어, RTRIM 콜레이션을 사용할 때 'text'와 'text '는 동일하게 취급됩니다(여기서 ' '는 공백 문자를 나타냅니다). 이 콜레이션은 데이터를 입력할 때 공백 처리의 일관성이 없는 경우 유용하게 사용할 수 있습니다.
CREATE TABLE Products (
    ProductID INTEGER PRIMARY KEY,
    ProductName TEXT COLLATE NOCASE
);

ProductName 열의 문자열 비교 시 대소문자를 구분하지 않도록 설정합니다.

 

GENERATED ALWAYS AS

GENERATED ALWAYS AS는 가상 열을 정의할 때 사용됩니다. 이 열은 다른 열의 값으로부터 계산되며, 직접 값을 삽입하거나 업데이트할 수 없습니다.

CREATE TABLE Employees (
    FirstName TEXT,
    LastName TEXT,
    FullName TEXT GENERATED ALWAYS AS (FirstName || ' ' || LastName)
);

FullName 열은 FirstName과 LastName의 값을 결합하여 자동으로 생성됩니다.

 

UNIQUE

UNIQUE 제약 조건은 열의 모든 값이 테이블 내에서 고유해야 함을 지정합니다. 중복된 값의 삽입이나 업데이트 시도 시 오류가 발생합니다.

CREATE TABLE Users (
    UserID INTEGER PRIMARY KEY,
    Username TEXT UNIQUE
);

Username 열의 값은 각 사용자마다 고유해야 합니다.

 

CHECK

CHECK 제약 조건은 열 값에 대해 지정된 조건을 만족해야 한다는 규칙을 설정합니다. 조건을 만족하지 않는 값의 삽입이나 업데이트 시도 시 오류가 발생합니다.

CREATE TABLE Products (
    ProductID INTEGER PRIMARY KEY,
    Price REAL CHECK (Price >= 0)
);

Price 열에는 0 이상의 값만 입력할 수 있습니다.

각 제약 조건과 옵션은 테이블 설계 시 데이터 무결성을 유지하고, 의도치 않은 데이터 오류를 방지하기 위해 중요한 역할을 합니다.

Previous
SQLite 언제 사용할까?
Next
외래 키