ALTER TABLE
ALTER TABLE
ALTER TABLE 페이지를 다룹니다.
출처: 문서
본문
1. 개요
alter-table-stmt:
ALTER TABLE schema-name . table-name RENAME TO new-table-name COLUMN column-name TO new-column-name ADD COLUMN column-def CONSTRAINT constraint-name CHECK ( expr ) conflict-clause DROP COLUMN column-name CONSTRAINT constraint-name ALTER COLUMN column-name SET NOT NULL conflict-clause DROP NOT NULL
column-def:
column-name type-name column-constraint
column-constraint:
CONSTRAINT name PRIMARY KEY DESC conflict-clause AUTOINCREMENT ASC NOT NULL conflict-clause UNIQUE conflict-clause CHECK ( expr ) DEFAULT ( expr ) literal-value signed-number COLLATE collation-name foreign-key-clause GENERATED ALWAYS AS ( expr ) VIRTUAL STORED
foreign-key-clause:
REFERENCES foreign-table ( column-name ) , ON DELETE SET NULL UPDATE SET DEFAULT CASCADE RESTRICT NO ACTION MATCH name NOT DEFERRABLE INITIALLY DEFERRED INITIALLY IMMEDIATE
literal-value:
CURRENT_TIMESTAMP numeric-literal string-literal blob-literal NULL TRUE FALSE CURRENT_TIME CURRENT_DATE
signed-number:
+ numeric-literal -
type-name:
name ( signed-number , signed-number ) ( signed-number )
signed-number:
+ numeric-literal -
conflict-clause:
ON CONFLICT ROLLBACK ABORT FAIL IGNORE REPLACE
expr:
literal-value bind-parameter schema-name . table-name . column-name unary-operator expr expr binary-operator expr function-name ( function-arguments ) filter-clause over-clause ( expr ) , CAST ( expr AS type-name ) expr COLLATE collation-name expr NOT LIKE GLOB REGEXP MATCH expr expr ESCAPE expr expr ISNULL NOTNULL NOT NULL expr IS NOT DISTINCT FROM expr expr NOT BETWEEN expr AND expr expr NOT IN ( select-stmt ) expr , schema-name . table-function ( expr ) table-name , NOT EXISTS ( select-stmt ) CASE expr WHEN expr THEN expr ELSE expr END raise-function
filter-clause:
FILTER ( WHERE expr )
function-arguments:
DISTINCT expr , * ORDER BY ordering-term ,
ordering-term:
expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST
literal-value:
CURRENT_TIMESTAMP numeric-literal string-literal blob-literal NULL TRUE FALSE CURRENT_TIME CURRENT_DATE
over-clause:
OVER window-name ( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )
frame-spec:
GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING RANGE ROWS UNBOUNDED PRECEDING expr PRECEDING CURRENT ROW expr PRECEDING CURRENT ROW expr FOLLOWING expr PRECEDING CURRENT ROW expr FOLLOWING EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS
ordering-term:
expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST
raise-function:
RAISE ( ROLLBACK , expr ) IGNORE ABORT FAIL
select-stmt:
WITH RECURSIVE common-table-expression , SELECT DISTINCT result-column , ALL FROM table-or-subquery join-clause , WHERE expr GROUP BY expr HAVING expr , WINDOW window-name AS window-defn , VALUES ( expr ) , , compound-operator select-core ORDER BY LIMIT expr ordering-term , OFFSET expr , expr
common-table-expression:
table-name ( column-name ) AS NOT MATERIALIZED ( select-stmt ) ,
compound-operator:
UNION UNION INTERSECT EXCEPT ALL
join-clause:
table-or-subquery join-operator table-or-subquery join-constraint
join-constraint:
USING ( column-name ) , ON expr
join-operator:
NATURAL LEFT OUTER JOIN , RIGHT FULL INNER CROSS
ordering-term:
expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST
result-column:
expr AS column-alias * table-name . *
table-or-subquery:
schema-name . table-name AS table-alias INDEXED BY index-name NOT INDEXED table-function-name ( expr ) , AS table-alias ( select-stmt ) ( table-or-subquery ) , join-clause
window-defn:
( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )
frame-spec:
GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING RANGE ROWS UNBOUNDED PRECEDING expr PRECEDING CURRENT ROW expr PRECEDING CURRENT ROW expr FOLLOWING expr PRECEDING CURRENT ROW expr FOLLOWING EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS
type-name:
name ( signed-number , signed-number ) ( signed-number )
signed-number:
+ numeric-literal -
SQLite는 ALTER TABLE의 제한된 하위 집합만 지원해요. SQLite의 ALTER TABLE 명령으로 기존 테이블에서 다음 변경을 할 수 있어요: 테이블 이름 바꾸기, 열 이름 바꾸기, 열 추가, 열 삭제.
2. ALTER TABLE RENAME (테이블 이름 바꾸기)
RENAME TO 구문은 table-name의 이름을 new-table-name으로 바꿔요. 이 명령은 부착된(attached) 데이터베이스 사이에서 테이블을 이동하는 데는 사용할 수 없고, 같은 데이터베이스 안에서만 테이블 이름을 바꿀 수 있어요. 이름을 바꾸는 테이블에 트리거나 인덱스가 있으면 이름이 바뀐 뒤에도 계속 그 테이블에 연결되어 있어요.
호환성 참고:
테이블 이름 바꾸기 시 ALTER TABLE 동작은 3.25.0(2018-09-15)과 3.26.0(2018-12-01)에서 개선되었어요. 이름이 바뀐 테이블을 참조하는 트리거와 뷰까지 이름 바꾸기 작업이 전달되도록 한 거예요. 이전(그리고 아마 버그였을) 동작에 의존하는 애플리케이션은 PRAGMA legacy_alter_table=ON 문이나 sqlite3_db_config() 인터페이스의 SQLITE_DBCONFIG_LEGACY_ALTER_TABLE 구성 매개변수를 사용해서 ALTER TABLE RENAME이 3.25.0 이전처럼 동작하게 할 수 있어요.
3.25.0(2018-09-15)부터는 트리거 본문과 뷰 정의 안에서 테이블을 참조하는 부분도 함께 이름이 바뀌어요.
3.26.0(2018-12-01) 이전에는 이름이 바뀌는 테이블에 대한 FOREIGN KEY 참조가 PRAGMA foreign_keys=ON일 때, 다시 말해 외래 키 제약 조건이 적용되는 중일 때만 수정되었어요. PRAGMA foreign_keys=OFF이면 외래 키가 참조하는 테이블(부모 테이블)의 이름이 바뀌어도 FOREIGN KEY 제약 조건은 변경되지 않았어요. 3.26.0부터는 PRAGMA legacy_alter_table=ON 설정이 켜져 있지 않으면 테이블 이름이 바뀔 때 FOREIGN KEY 제약 조건도 항상 변환돼요. 다음 표는 차이를 요약한 거예요.
| PRAGMA foreign_keys | PRAGMA legacy_alter_table | 부모 테이블 참조 업데이트 | SQLite 버전 |
|---|---|---|---|
| Off | Off | 아니요 | < 3.26.0 |
| Off | Off | 예 | >= 3.26.0 |
| On | Off | 예 | 모든 버전 |
| Off | On | 아니요 | 모든 버전 |
| On | On | 예 | 모든 버전 |
3. ALTER TABLE RENAME COLUMN: 열 이름 변경
RENAME COLUMN TO 구문은 table-name 테이블의 column-name을 new-column-name으로 바꿔요. 열 이름은 테이블 정의 자체와 그 열을 참조하는 모든 인덱스, 트리거, 뷰 안에서 함께 변경돼요. 열 이름 변경으로 인해 트리거나 뷰에서 의미상 모호함이 생기면 RENAME COLUMN은 오류와 함께 실패하고 아무 변경도 적용되지 않아요.
4. ALTER TABLE ADD COLUMN (열 추가)
ADD COLUMN 구문은 기존 테이블에 새 열을 추가할 때 사용해요. 새 열은 항상 기존 열 목록의 맨 끝에 추가돼요. column-def 규칙이 새 열의 특성을 정의해요. 새 열은 CREATE TABLE 문에서 허용되는 어떤 형태든 가질 수 있지만, 다음 제한 사항이 있어요.
- 열에는 PRIMARY KEY 또는 UNIQUE 제약 조건이 있으면 안 돼요.
- 열의 기본값이 CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP 또는 괄호로 묶인 표현식이면 안 돼요.
- NOT NULL 제약 조건이 지정된 경우 열은 NULL이 아닌 기본값을 가져야 해요.
- 외래 키 제약 조건이 활성화되어 있고 REFERENCES 절이 있는 열을 추가하는 경우 열의 기본값은 NULL이어야 해요.
- 열은 GENERATED ALWAYS ... STORED가 될 수 없어요. VIRTUAL 열은 허용돼요.
CHECK 제약 조건이 있는 열이나 생성 열(generated column)에 NOT NULL 제약 조건이 있는 열을 추가할 때는 추가된 제약 조건을 테이블의 모든 기존 행에 대해 검사하며, 하나라도 실패하면 ADD COLUMN은 실패해요. 추가된 제약 조건을 기존 행에 검사하는 기능은 SQLite 버전 3.37.0(2021-11-27)부터 새로 개선된 부분이에요.
ALTER TABLE 명령은 sqlite_schema 테이블에 저장된 스키마의 SQL 텍스트를 수정하는 방식으로 동작해요. 이름 바꾸기나 제약 조건 없는 열 추가 시에는 테이블 내용이 변경되지 않아요. 그래서 이런 ALTER TABLE 명령의 실행 시간은 테이블의 데이터 양과 무관해요. 1,000만 개의 행이 있는 테이블에서도 1개의 행이 있는 테이블과 같은 속도로 실행돼요. CHECK 제약 조건이 있는 새 열을 추가하거나, NOT NULL 제약 조건이 있는 생성 열을 추가하거나, 열을 삭제할 때는 테이블의 모든 기존 데이터를 읽거나(기존 행에 새 제약 조건을 검사하려고) 써야 해요(삭제된 열을 제거하려고). 이런 경우 ALTER TABLE 명령은 변경되는 테이블의 내용 양에 비례하는 시간이 걸려요.
데이터베이스에서 ADD COLUMN을 실행한 후에는 그 데이터베이스를 SQLite 버전 3.1.3(2005-02-20) 및 이전 버전에서 읽을 수 없어요.
5. ALTER TABLE DROP COLUMN (열 삭제)
DROP COLUMN 구문은 테이블에서 기존 열을 제거하는 데 사용해요. DROP COLUMN 명령은 이름이 지정된 열을 테이블에서 제거하고, 해당 열과 연관된 데이터를 제거하기 위해 내용을 다시 작성해요. DROP COLUMN 명령은 열이 스키마의 다른 어떤 부분에서도 참조되지 않고, PRIMARY KEY가 아니며, UNIQUE 제약 조건이 없는 경우에만 작동해요. DROP COLUMN 명령이 실패할 수 있는 이유는 다음과 같아요.
- 열이 PRIMARY KEY 또는 그 일부예요.
- 열에 UNIQUE 제약 조건이 있어요.
- 열이 인덱싱되어 있어요.
- 부분 인덱스의 WHERE 절에 열 이름이 지정되어 있어요.
- 삭제할 열과 연관되지 않은 테이블 또는 열 CHECK 제약 조건에 열 이름이 지정되어 있어요.
- 열이 외래 키 제약 조건에 사용돼요.
- 열이 생성된 열의 표현식에 사용돼요.
- 열이 트리거 또는 뷰에 나타나요.
5.1. 작동 방식
SQLite는 sqlite_schema 테이블에 스키마를 일반 텍스트로 저장해요. 모든 ALTER TABLE 명령은 해당 텍스트를 수정한 다음 전체 스키마를 다시 파싱하려고 시도해요. 텍스트가 수정된 후에도 스키마가 여전히 유효한 경우에만 명령이 성공해요. DROP COLUMN 명령의 경우 수정되는 텍스트는 CREATE TABLE 문에서 열 정의가 제거되는 것뿐이에요. CREATE TABLE 문이 수정된 후 스키마가 파싱되지 못하게 하는 열의 흔적이 스키마의 다른 부분에 남아 있으면 DROP COLUMN 명령은 실패해요.
6. ALTER TABLE ALTER COLUMN (열 변경)
ALTER TABLE ALTER COLUMN 구문을 사용하여 열에서 NOT NULL 제약 조건을 설정하거나 제거하는 기능은 SQLite 3.53.0 (2026-04-09)에 추가되었어요.
ALTER TABLE *tabname* ALTER *column* SET NOT NULL 명령은 중복된 NOT NULL 제약 조건을 추가하지 않아요. 열이 이미 NOT NULL이면 SET NOT NULL은 아무 작업도 하지 않아요. 하지만 CREATE TABLE을 사용하여 중복된 NOT NULL 제약 조건이 있는 열을 지정하는 것은 가능해요. 단일 열에 이미 두 개 이상의 NOT NULL 제약 조건이 있는 경우, ALTER TABLE *tabname* ALTER *column* DROP NOT NULL 명령은 그중 하나 이상을 제거하지만 반드시 모두 제거하지는 않아요.
7. PRAGMA writable_schema=ON을 사용하여 오류 검사 비활성화
ALTER TABLE은 일반적으로 파싱되지 않는 sqlite_schema 테이블의 항목을 발견하면 실패하고 아무 변경도 하지 않아요. 예를 들어, "tbl1"이라는 테이블과 연결된 잘못된 VIEW 또는 TRIGGER가 있으면 "tbl1"을 "tbl1neo"로 이름을 바꾸려는 시도는 연결된 뷰와 트리거를 파싱할 수 없기 때문에 실패해요.
SQLite 3.38.0 (2022-02-22)부터 이 오류 검사는 "PRAGMA writable_schema=ON;"으로 설정하여 비활성화할 수 있어요. 스키마를 쓰기 가능하게 설정하면 ALTER TABLE은 파싱되지 않는 sqlite_schema 테이블의 모든 행을 조용히 무시해요.
8. 다른 종류의 테이블 스키마 변경 만들기
SQLite가 직접 지원하는 유일한 스키마 변경 명령은 위에 표시된 "rename table", "rename column", "add column", "drop column" 명령이에요. 하지만 응용 프로그램은 간단한 일련의 작업을 사용하여 테이블 형식에 대해 다른 임의의 변경을 할 수 있어요. 일부 테이블 X의 스키마 설계를 임의로 변경하는 단계는 다음과 같아요.
- 외래 키 제약 조건이 활성화되어 있으면 PRAGMA foreign_keys=OFF를 사용하여 비활성화하세요.
- 트랜잭션을 시작하세요.
- 테이블 X와 연결된 모든 인덱스, 트리거 및 뷰의 형식을 기억하세요. 이 정보는 아래 8단계에서 필요해요. 이를 수행하는 한 가지 방법은 다음과 같은 쿼리를 실행하는 거예요.
SELECT type, sql FROM sqlite_schema WHERE tbl_name='X';
- CREATE TABLE을 사용하여 원하는 수정된 형식의 테이블 X로 새 테이블 "new_X"를 구성하세요. 물론 "new_X"라는 이름이 기존 테이블 이름과 충돌하지 않도록 확인하세요.
- 다음과 같은 문을 사용하여 X의 내용을 new_X로 전송하세요:
INSERT INTO new_X SELECT ... FROM X. - 이전 테이블 X를 삭제하세요:
DROP TABLE X. - new_X의 이름을 X로 변경하세요:
ALTER TABLE new_X RENAME TO X. - CREATE INDEX, CREATE TRIGGER 및 CREATE VIEW를 사용하여 테이블 X와 연결된 인덱스, 트리거 및 뷰를 재구성하세요. 위 3단계에서 저장된 트리거, 인덱스 및 뷰의 이전 형식을 참조로 사용하고 변경 사항에 맞게 수정하는 것이 좋아요.
- 스키마 변경의 영향을 받는 방식으로 테이블 X를 참조하는 뷰가 있으면 DROP VIEW를 사용하여 해당 뷰를 삭제하고, CREATE VIEW를 사용하여 스키마 변경을 수용하는 데 필요한 변경 사항을 적용하여 다시 만드세요.
- 외래 키 제약 조건이 원래 활성화된 경우 PRAGMA foreign_key_check를 실행하여 스키마 변경이 외래 키 제약 조건을 위반하지 않는지 확인하세요.
- 2단계에서 시작한 트랜잭션을 커밋하세요.
- 외래 키 제약 조건이 원래 활성화된 경우 지금 다시 활성화하세요.
주의: 위 절차를 정확히 따르도록 주의하세요. 아래 상자들은 테이블 정의를 수정하는 두 가지 절차를 요약한 것이에요. 언뜻 보면 둘 다 동일한 작업을 수행하는 것처럼 보여요. 그러나 오른쪽 절차는 항상 작동하지는 않아요. 특히 3.25.0 및 3.26.0 버전에서 추가된 향상된 rename table 기능이 있는 경우 더욱 그래요. 오른쪽 절차에서 테이블을 임시 이름으로 처음 바꾸면 트리거, 뷰 및 외래 키 제약 조건에서 해당 테이블에 대한 참조가 손상될 수 있어요. 왼쪽의 안전한 절차는 새 임시 이름을 사용하여 수정된 테이블 정의를 구성한 다음 테이블을 최종 이름으로 바꾸므로 링크가 끊어지지 않아요.
| - 새 테이블 생성 - 데이터 복사 - 이전 테이블 삭제 - 새 테이블을 이전 이름으로 바꾸기 |
- 이전 테이블 이름 바꾸기 - 새 테이블 생성 - 데이터 복사 - 이전 테이블 삭제 |
|
| ↑ 올바른 방법 | ↑ 잘못된 방법 |
위의 12단계 일반 ALTER TABLE 절차는 스키마 변경으로 인해 테이블에 저장된 정보가 변경되는 경우에도 작동해요. 따라서 위의 전체 12단계 절차는 예를 들어 열 삭제, 열 순서 변경, UNIQUE 제약 조건 또는 PRIMARY KEY 추가/제거, CHECK 또는 FOREIGN KEY 또는 NOT NULL 제약 조건 추가, 열의 데이터 타입 변경 등에 적합해요. 그러나 디스크의 내용에 전혀 영향을 주지 않는 일부 변경에는 더 간단하고 빠른 절차를 선택적으로 사용할 수 있어요. 다음의 간단한 절차는 CHECK 또는 FOREIGN KEY 또는 NOT NULL 제약 조건을 제거하거나 열의 기본값을 추가, 제거 또는 변경하는 데 적합해요.
- 트랜잭션을 시작하세요.
- 현재 스키마 버전 번호를 확인하려면 PRAGMA schema_version을 실행하세요. 이 번호는 아래 6단계에서 필요해요.
- PRAGMA writable_schema=ON을 사용하여 스키마 편집을 활성화하세요.
- sqlite_schema 테이블에서 테이블 X의 정의를 변경하려면 UPDATE 문을 실행하세요.
UPDATE sqlite_schema SET sql=... WHERE type='table' AND name='X';
주의: 이와 같이 sqlite_schema 테이블을 변경하면 변경 내용에 구문 오류가 포함된 경우 데이터베이스가 손상되고 읽을 수 없게 돼요. 중요한 데이터가 포함된 데이터베이스에 적용하기 전에 별도의 빈 데이터베이스에서 UPDATE 문을 신중하게 테스트하는 것이 좋아요.
- 테이블 X의 변경이 스키마 내의 다른 테이블, 인덱스, 트리거 또는 뷰에도 영향을 미치는 경우 해당 다른 테이블, 인덱스 및 뷰도 수정하는 UPDATE 문을 실행하세요. 예를 들어, 열 이름이 변경되면 해당 열을 참조하는 모든 FOREIGN KEY 제약 조건, 트리거, 인덱스 및 뷰를 수정해야 해요.
주의: 다시 한번, 이와 같이 sqlite_schema 테이블을 변경하면 변경 내용에 오류가 있는 경우 데이터베이스가 손상되고 읽을 수 없게 돼요. 중대한 데이터가 포함된 데이터베이스에 적용하기 전에 별도의 테스트 데이터베이스에서 전체 절차를 신중하게 테스트하고/하거나 이 절차를 실행하기 전에 중요한 데이터베이스의 백업 복사본을 만들어 두세요.
- PRAGMA schema_version=X를 사용하여 스키마 버전 번호를 증가시키세요. 여기서 X는 위 2단계에서 찾은 이전 스키마 버전 번호보다 1 큰 값이에요.
- PRAGMA writable_schema=OFF를 사용하여 스키마 편집을 비활성화하세요.
- (선택 사항) PRAGMA integrity_check를 실행하여 스키마 변경이 데이터베이스를 손상시키지 않았는지 확인하세요.
- 위 1단계에서 시작한 트랜잭션을 커밋하세요.
SQLite의 향후 버전에서 새로운 ALTER TABLE 기능이 추가된다면, 그 기능들은 위에 설명된 두 절차 중 하나를 사용할 가능성이 매우 높아요.
## 9. SQLite에서 ALTER TABLE이 왜 그렇게 문제가 되는가
대부분의 SQL 데이터베이스 엔진은 스키마를 이미 파싱된 형태로 다양한 시스템 테이블에 저장해요. 이러한 데이터베이스 엔진에서 ALTER TABLE은 해당 시스템 테이블을 수정하기만 하면 돼요.
SQLite는 스키마를 정의하는 CREATE 문의 원본 텍스트로 sqlite_schema 테이블에 저장한다는 점에서 달라요. 따라서 ALTER TABLE은 CREATE 문의 텍스트를 수정해야 해요. 이는 특정 "창의적인" 스키마 설계의 경우 까다로울 수 있어요.
스키마를 텍스트로 저장하는 SQLite의 접근 방식은 임베디드 관계형 데이터베이스에 이점이 있어요. 우선 스키마가 데이터베이스 파일에서 더 적은 공간을 차지한다는 뜻이에요. 일반적인 SQLite 사용 패턴은 모든 것을 하나의 큰 전역 데이터베이스 파일에 넣는 클라이언트/서버 데이터베이스 엔진의 일반적인 방식 대신, 작고 분리된 여러 데이터베이스 파일을 두는 것이기 때문에 이는 중요해요. 스키마가 각각의 분리된 데이터베이스 파일에 중복되므로 스키마 표현을 간결하게 유지하는 것이 중요해요.
스키마를 파싱된 테이블이 아닌 텍스트로 저장하는 것은 구현에 유연성도 제공해요. 데이터베이스가 열릴 때마다 스키마의 내부 파싱이 다시 생성되므로 스키마의 내부 표현은 릴리스마다 변경될 수 있어요. 이는 중요한데, 때로는 새 기능이 내부 스키마 표현의 개선을 요구하기 때문이에요. 스키마 표현이 데이터베이스 파일에 노출되었다면 내부 스키마 표현을 변경하는 것이 훨씬 더 어려웠을 거예요. 즉, 스키마를 텍스트로 저장하면 이전 버전과의 호환성을 유지하고, 이전 데이터베이스 파일을 최신 버전의 SQLite로 읽고 쓸 수 있도록 보장하는 데 도움이 돼요.
스키마를 텍스트로 저장하는 것은 SQLite 데이터베이스 파일 형식을 더 쉽게 정의하고 문서화하고 이해할 수 있게 만들어요. 이는 SQLite 데이터베이스 파일을 장기 데이터 보관을 위한 권장 저장 형식으로 만드는 데 도움이 돼요.
스키마를 텍스트로 저장할 때의 단점은 스키마 수정이 까다로울 수 있다는 것이에요. 그렇기 때문에 SQLite의 ALTER TABLE 지원은 전통적으로 수정하기 쉬운 파싱된 시스템 테이블로 스키마를 저장하는 다른 SQL 데이터베이스 엔진보다 뒤처져 있어요.
이 페이지는 2026-06-04 01:35:31Z에 마지막으로 업데이트되었어요.