이 랩에서는 데이터 무결성을 보장하기 위해 SQLite 제약 조건 관리를 탐구합니다. 먼저 외래 키 제약 조건 (foreign key constraints) 을 정의하여 테이블 간의 관계를 설정하고, CHECK 제약 조건을 구현하며, 복합 키 (composite keys) 를 생성한 다음, 제약 조건 위반을 테스트하여 SQLite 가 이러한 규칙을 어떻게 적용하는지 이해합니다. 이 실습 경험을 통해 SQLite 데이터베이스 내에서 데이터 일관성을 유지하는 방법에 대한 실질적인 이해를 얻을 수 있습니다.
이 단계에서는 customers와 orders 두 개의 테이블을 생성하고, 이들 간에 외래 키 제약 조건을 설정합니다. 이 제약 조건은 각 주문이 유효한 고객과 연결되도록 보장합니다.
먼저, 터미널에서 다음 명령을 실행하여 SQLite 셸을 엽니다.
sqlite3 /home/labex/project/database.db이 명령은 database.db라는 SQLite 데이터베이스 파일에 대한 연결을 엽니다. 파일이 존재하지 않으면 SQLite 가 파일을 생성합니다. 이제 sqlite> 프롬프트를 볼 수 있습니다.
이제 다음 SQL 명령을 사용하여 customers 테이블을 생성합니다.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
email TEXT
);이 명령은 customer_id, first_name, last_name, email의 네 개의 열을 가진 customers라는 테이블을 생성합니다. customer_id 열은 기본 키이며, 각 새 고객에 대해 자동으로 증가합니다.
다음으로, customers 테이블을 참조하는 외래 키 제약 조건이 있는 orders 테이블을 생성합니다.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER,
order_date TEXT,
total REAL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);이 명령은 order_id, customer_id, order_date, total의 네 개의 열을 가진 orders라는 테이블을 생성합니다. order_id 열은 기본 키입니다. FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 절은 외래 키 제약 조건을 설정하여 orders 테이블의 customer_id가 customers 테이블의 유효한 customer_id를 참조하도록 보장합니다.
테이블이 생성되었는지 확인하려면 다음 명령을 사용할 수 있습니다.
.tables이 명령은 데이터베이스의 모든 테이블을 나열합니다. 출력에서 customers와 orders를 모두 볼 수 있습니다.
이 단계에서는 customers 및 orders 테이블에 데이터를 삽입하고 외래 키 제약 조건을 테스트합니다.
먼저, SQLite 에서 외래 키 제약 조건을 활성화해야 합니다 (기본적으로 비활성화되어 있습니다).
PRAGMA foreign_keys = ON;이제 customers 테이블에 데이터를 삽입합니다.
INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com');이 명령은 customers 테이블에 두 명의 새로운 고객을 삽입합니다. customer_id는 자동으로 할당됩니다.
이제 기존 고객 중 하나를 참조하여 orders 테이블에 주문을 삽입합니다.
INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2023-01-01', 100.00);이 명령은 customer_id가 1 인 고객과 연결된 새로운 주문을 orders 테이블에 삽입합니다.
다음으로, customers 테이블에 존재하지 않는 customer_id로 주문을 삽입해 봅니다.
INSERT INTO orders (customer_id, order_date, total) VALUES
(99, '2023-01-02', 50.00);다음과 유사한 오류 메시지가 표시됩니다: Error: FOREIGN KEY constraint failed. 이는 외래 키 제약 조건이 올바르게 작동하여 존재하지 않는 고객에 대한 주문 생성을 방지함을 확인합니다.
테이블의 데이터를 보려면 다음 명령을 사용할 수 있습니다.
SELECT * FROM customers;
SELECT * FROM orders;이 명령은 각각 customers 및 orders 테이블의 내용을 표시합니다.
이 단계에서는 모든 이메일 주소에 @ 기호가 포함되도록 보장하는 CHECK 제약 조건을 포함하는 새로운 customers_with_check 테이블을 생성합니다.
먼저, CHECK 제약 조건으로 새 테이블을 직접 생성해 보겠습니다.
CREATE TABLE customers_with_check (
customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
email TEXT CHECK (email LIKE '%@%')
);이 명령은 email 열에 @ 기호가 포함되도록 보장하는 CHECK 제약 조건이 있는 customers_with_check라는 새 테이블을 생성합니다. LIKE 연산자는 패턴 일치에 사용되며, %는 모든 문자 시퀀스와 일치하는 와일드카드 문자입니다.
이제 기존 고객 데이터를 새 테이블로 복사해 보겠습니다.
INSERT INTO customers_with_check (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email FROM customers;이제 유효하지 않은 이메일 주소로 새 고객을 삽입해 봅니다.
INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Charlie', 'Davis', 'invalid-email');다음과 유사한 오류 메시지가 표시됩니다: Error: CHECK constraint failed: email. 이는 CHECK 제약 조건이 유효하지 않은 이메일 주소의 삽입을 방지했음을 나타냅니다.
CHECK 제약 조건이 작동하는지 확인하려면 유효한 이메일 주소로 고객을 삽입합니다.
INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Alice', 'Brown', 'alice.brown@example.com');이 명령은 이메일 주소에 @ 기호가 포함되어 있으므로 성공적으로 실행되어야 합니다.
이 단계에서는 student_id 및 course_id 열로 구성된 복합 키가 있는 enrollments라는 테이블을 생성합니다.
SQLite 셸에서 다음 SQL 문을 실행합니다.
CREATE TABLE enrollments (
student_id INTEGER,
course_id INTEGER,
enrollment_date TEXT,
PRIMARY KEY (student_id, course_id)
);이 명령은 student_id, course_id, 및 enrollment_date의 세 개의 열이 있는 enrollments라는 테이블을 생성합니다. PRIMARY KEY (student_id, course_id) 절은 테이블의 기본 키가 student_id 및 course_id 열 모두로 구성됨을 지정합니다. 즉, student_id와 course_id의 조합은 테이블의 각 행에 대해 고유해야 합니다.
이제 enrollments 테이블에 데이터를 삽입합니다.
INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-01'),
(2, 101, '2023-01-02'),
(1, 102, '2023-01-03');이렇게 하면 enrollments 테이블에 세 개의 행이 삽입됩니다.
다음으로, 기존 행과 동일한 student_id 및 course_id를 가진 행을 삽입해 봅니다.
INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-04');다음과 유사한 오류 메시지가 표시됩니다: Error: UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. 이는 복합 키 제약 조건이 중복 행의 삽입을 방지했음을 나타냅니다.
테이블이 올바르게 생성되었는지 확인하려면 SQLite 셸에서 .tables 명령을 사용할 수 있습니다.
.tables출력에서 enrollments를 볼 수 있습니다.
이 Lab 에서는 데이터 무결성을 보장하기 위해 SQLite 에서 제약 조건을 정의하고 구현하는 방법을 배웠습니다. 외래 키 (foreign key) 제약 조건이 있는 테이블을 생성하고, 데이터를 검증하기 위해 CHECK 제약 조건을 구현했으며, 여러 열을 기반으로 행을 고유하게 식별하기 위해 복합 키 (composite key) 가 있는 테이블을 생성했습니다. 이러한 제약 조건을 이해하고 활용함으로써 데이터 일관성을 유지하는 강력하고 신뢰할 수 있는 데이터베이스를 구축할 수 있습니다.
