SQLite의 두 열 사이에 새 열 추가

SQLite에는 ALTER TABLE ... ADD COLUMN에서 열 위치를 지정하는 옵션이 없습니다. 새 열은 기존 열 뒤에 추가됩니다. 두 열 사이에 새 열을 넣으려면 원하는 열 순서로 대체 테이블을 만들고, 기존 데이터를 복사한 다음 원래 테이블을 대체해야 합니다. 아래 예제에서는 customers 테이블의 first_name과 last_name 사이에 phone_number를 추가합니다.

SQLite에서 기존 열 사이에 새 열을 추가할 수 없는 이유

SQLite의 ALTER TABLE 문서는 ADD COLUMN이 테이블 정의의 맨 끝에 열을 추가하는 작업이라고 설명합니다. 열 위치를 지정하는 FIRST 또는 AFTER 절은 없습니다. 예를 들어 ALTER TABLE customers ADD COLUMN phone_number TEXT는 phone_number를 끝에 추가하므로 first_name과 last_name 사이에 배치할 수 없습니다.

새 열이 존재하기만 하면 된다면 끝에 추가하는 편이 간단하고 테이블을 다시 만들 필요도 없습니다. 열 순서가 열 번호를 기준으로 읽는 소비자에게 중요하다면 테이블을 다시 만드세요. 애플리케이션 쿼리에서는 열 이름을 명시해 물리적인 열 순서에 따라 동작이 달라지지 않도록 하는 것이 좋습니다.

원하는 위치에 열을 배치하도록 테이블 다시 만들기

필요한 모든 열이 의도한 순서로 들어가도록 대체 테이블 정의를 작성합니다. 기존 자료형, 기본 키, NULL 허용 여부, 기본값, 고유성 규칙과 기타 제약 조건을 유지하세요. 이 간단한 예제는 인메모리 데이터베이스와 독립된 테이블을 사용하므로 외부 파일이나 서비스 없이 마이그레이션을 보여 줍니다.

다음은 완전한 Python 예제입니다.

이 섹션의 코드와 표시되는 레이블 또는 출력은 영어로 유지됩니다.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL UNIQUE)")
connection.executemany(
    "INSERT INTO customers (customer_id, first_name, last_name, email) VALUES (?, ?, ?, ?)",
    [(1, "Ada", "Lovelace", "ada@example.com"), (2, "Grace", "Hopper", "grace@example.com")],
)
connection.execute("CREATE TABLE customers_new (customer_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, phone_number TEXT NOT NULL DEFAULT '', last_name TEXT NOT NULL, email TEXT NOT NULL UNIQUE)")
connection.execute("INSERT INTO customers_new (customer_id, first_name, phone_number, last_name, email) SELECT customer_id, first_name, '', last_name, email FROM customers")
connection.execute("DROP TABLE customers")
connection.execute("ALTER TABLE customers_new RENAME TO customers")
print([column[1] for column in connection.execute("PRAGMA table_info(customers)")])
print(connection.execute("SELECT customer_id, first_name, phone_number, last_name, email FROM customers ORDER BY customer_id").fetchall())
connection.close()

['customer_id', 'first_name', 'phone_number', 'last_name', 'email']
[(1, 'Ada', '', 'Lovelace', 'ada@example.com'), (2, 'Grace', '', 'Hopper', 'grace@example.com')]

제공된 런타임 실행에서는 위에 표시된 열 순서와 행이 출력됩니다. 예제는 기존 고객의 새 전화번호에 빈 문자열을 사용합니다. 실제 데이터에 맞는 값이나 마이그레이션 규칙을 적용하려면 해당 식을 바꾸세요. 테이블 정의와 두 열 목록을 명시했으므로 새 열이 의도한 위치에 놓이고 기존 값은 각각 대응하는 대상 열로 복사됩니다.

기존 데이터를 다시 만든 테이블에 복사하기

INSERT INTO ... SELECT 문은 원래 테이블의 행을 복사합니다. 대상 목록에는 대체 테이블의 열을 지정하고, SELECT 목록에는 같은 순서로 값을 제공합니다. 여기서 phone_number에 해당하는 원본 열이 없으므로 쿼리는 기존 행에 ''를 넣습니다. 대신 적절한 기본값이나 고정값을 사용하거나 기존 데이터를 바탕으로 값을 변환할 수 있습니다. 새 열에 NOT NULL 제약 조건이 적용된다면 특히 마이그레이션 전에 그 열을 어떻게 채울지 정하세요.

SELECT *는 사용하지 마세요. 이전 테이블과 새 테이블의 열 구성이 다릅니다. 열 목록을 명시하면 값의 대응 관계가 분명해지고 스키마가 바뀌어도 값이 잘못된 열로 이동하는 일을 막을 수 있습니다.

이전 테이블을 대체하고 결과 확인하기

대체 테이블에 데이터를 채우고 복구 계획을 마련하기 전에는 원래 테이블을 삭제하지 마세요. 운영 환경의 테이블에는 인덱스, 트리거, 외래 키 관계 또는 종속된 뷰가 있을 수 있습니다. 마이그레이션 중에 필요한 스키마 객체를 다시 만들고 외래 키 종속성도 고려하세요. 간단한 예제에는 이런 요소가 포함되어 있지 않습니다. 중요한 데이터는 백업하고 주변 스키마에 적절한 트랜잭션 안에서 테이블을 다시 만드세요.

대체한 뒤에는 PRAGMA table_info(customers)로 스키마를 살펴보고, 열을 명시한 SELECT 문으로 대표 행을 확인하세요. 행 수와 애플리케이션에 필요한 제약 조건 및 종속 객체도 점검합니다. 이렇게 확인하면 기존 값과 필요한 스키마 동작을 유지하면서 열 순서가 바뀌었는지 검증할 수 있습니다.