데이터베이스 트랜잭션 및 테이블 관리

데이터베이스의 트랜잭션과 테이블 관리는 데이터의 무결성을 유지하고 효율적인 데이터 관리를 가능하게 합니다. 이 포스팅에서는 트랜잭션의 개념, COMMIT과 ROLLBACK, SAVEPOINT, 자동 커밋 및 롤백, ALTER TABLE 구문, CREATE / DROP TABLE 등의 내용을 상세히 설명하겠습니다.

1. 트랜잭션 시작과 종료

트랜잭션 시작

• 트랜잭션은 실행 가능한 첫 번째 SQL 문장이 실행될 때 시작됩니다. 이 시점부터 이후의 모든 SQL 문장은 하나의 논리적인 작업 단위로 묶입니다. 이를 통해 여러 작업을 함께 처리하거나 실패 시 롤백할 수 있습니다.

트랜잭션 종료

• 트랜잭션은 명시적으로 COMMIT 또는 ROLLBACK 명령어로 종료됩니다. 또한, 자동 커밋이 발생할 때도 종료됩니다. 자동 커밋은 DDL(데이터 정의 언어) 문장이 완료될 때, DCL(데이터 제어 언어) 문장이 완료될 때, 또는 SQL Plus와 같은 데이터베이스 시스템이 비정상적으로 종료될 때 발생합니다.

2. COMMIT과 ROLLBACK

COMMIT

• COMMIT은 트랜잭션에서 발생한 모든 변경 사항을 데이터베이스에 영구적으로 반영하는 명령어입니다. COMMIT이 실행되면 해당 트랜잭션의 작업이 성공적으로 완료되었다고 간주됩니다.

ROLLBACK

• ROLLBACK은 트랜잭션에서 발생한 변경 사항을 마지막 COMMIT 이전의 상태로 되돌립니다. 주로 오류가 발생했을 때 사용됩니다.

3. SAVEPOINT

SAVEPOINT

• SAVEPOINT는 트랜잭션 내에서 특정 시점을 지정하여, 해당 시점까지만 롤백할 수 있는 기능입니다. 복잡한 트랜잭션 처리에서 중간 결과를 보존하고 트랜잭션의 일부만 롤백할 수 있도록 해줍니다.

4. 자동 커밋과 자동 롤백

자동 커밋

• 일반적으로 DDL 문장이 실행될 때와 DCL 문장이 실행될 때 자동으로 커밋됩니다. SQL 세션이 종료될 때도 자동으로 커밋이 이루어집니다.

자동 롤백

• SQL 비정상 종료나 시스템 실패 시에는 자동으로 롤백됩니다. 이는 데이터베이스의 무결성을 유지하기 위한 조치입니다.

SET AUTOCOMMIT ON

• 이 옵션을 설정하면 각 SQL 문장이 실행될 때마다 자동으로 COMMIT됩니다. 이는 실수로 인한 데이터 손실을 방지하지만, 롤백이 불가능하여 데이터를 되돌릴 수 없다는 단점이 있습니다.

5. ALTER TABLE 구문 설명

열 이름 변경

• RENAME COLUMN: 테이블의 열 이름을 변경할 때 사용하는 구문입니다.

  ALTER TABLE 테이블명 RENAME COLUMN 기존_열명 TO 새로운_열명;

  예시:

  ALTER TABLE Employees RENAME COLUMN EmpFirstName TO FirstName;

열 수정

• MODIFY: 테이블의 열의 데이터 타입이나 크기를 수정할 때 사용합니다. 단, 데이터 타입의 크기는 줄일 수 없습니다.

  ALTER TABLE 테이블명 MODIFY (열명 데이터타입);

  예시:

  ALTER TABLE Products MODIFY (Price DECIMAL(10, 2));

열 삭제

• DROP COLUMN: 테이블에서 열을 삭제할 때 사용합니다.

  ALTER TABLE 테이블명 DROP COLUMN 열명;

  예시:

  ALTER TABLE Students DROP COLUMN GPA;

6. CREATE / DROP TABLE

1. 테이블 생성 (CREATE TABLE)

• CREATE TABLE: 새로운 테이블을 생성할 때 사용하는 명령어로, 각 열의 데이터 타입과 제약 조건을 정의합니다.

  예시:

  CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2)
  );

2. 테이블 구조 변경 (ALTER TABLE)

• ALTER TABLE: 기존 테이블의 구조를 변경할 때 사용됩니다. 열 추가, 삭제, 이름 변경, 데이터 타입 변경 등이 가능합니다.

  • 열 추가:

    ALTER TABLE employees ADD phone_number VARCHAR2(20);

  • 열 삭제:

    ALTER TABLE employees DROP COLUMN phone_number;

  • 열 이름 변경:

    ALTER TABLE employees RENAME COLUMN phone_number TO contact_number;

  • 열 데이터 타입 변경:

    ALTER TABLE employees MODIFY salary NUMBER(10,2);

3. 테이블 삭제 (DROP TABLE)

• DROP TABLE: 테이블 자체를 삭제할 때 사용됩니다. 이 명령어를 실행하면 테이블의 모든 데이터와 구조가 삭제됩니다.

  예시:

  DROP TABLE employees;

4. 테이블 데이터 비우기 (TRUNCATE)

• TRUNCATE: 테이블의 모든 데이터를 삭제하지만, 테이블 구조는 유지합니다. 테이블의 모든 행을 삭제하여 초기 상태로 되돌립니다.

  예시:

  TRUNCATE TABLE employees;

추가 내용

• 트랜잭션의 분리되어서는 안 될 작업의 단위: 트랜잭션 내의 모든 작업은 반드시 함께 실행되어야 하며, 분리되어서는 안 됩니다.

• DDL과 DCL에 의한 자동 커밋: DDL (예: CREATE, ALTER, DROP) 또는 DCL (예: GRANT, REVOKE) 문장이 실행되면 자동으로 커밋됩니다.

• FOREIGN KEY 제약 조건: 외래 키 제약은 데이터 무결성을 유지하기 위해 사용됩니다. 외래 키가 참조하는 기본 키가 존재해야 합니다.

  예시:

  CREATE TABLE Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
  );