Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

ALTER TABLE

Statements

NAME

ALTER TABLE: change a base table

SYNOPSIS

<alter table statement> ::=
    ALTER TABLE <table name> <alter table action>

<alter table action> ::=
      ADD [ COLUMN ] <column definition>
    | ALTER [ COLUMN ] <column name> <alter column action>
    | DROP [ COLUMN ] <column name> <drop behavior>
    | ADD <table constraint definition>
    | DROP CONSTRAINT <constraint name> <drop behavior>
    | ADD <table period definition>
      [ ADD [ COLUMN ] <column definition> ADD [ COLUMN ] <column definition> ]
    | DROP PERIOD FOR { <period name> | SYSTEM_TIME } <drop behavior>
    | ADD SYSTEM VERSIONING
    | DROP SYSTEM VERSIONING <drop behavior>
    | SET ( <table option> [ { , <table option> }... ] )
    | USING <engine name> [ WITH ( <table option> [ { , <table option> }... ] ) ]
    | RESEAL
    | RENAME [ COLUMN ] <column name> TO <column name>
    | ATTACH PARTITION <table name> <partition bound>
    | DETACH PARTITION <table name>

<alter column action> ::=
      SET DEFAULT <default option>
    | DROP DEFAULT
    | <alter identity column option>...
    | SET GENERATED { ALWAYS | BY DEFAULT } [ <alter identity column option>... ]
    | DROP IDENTITY
    | DROP EXPRESSION
    | ADD SCOPE <table name>
    | DROP SCOPE <drop behavior>

<alter identity column option> ::=
      RESTART [ WITH <signed numeric literal> ]
    | SET INCREMENT BY <signed numeric literal>
    | SET MAXVALUE <signed numeric literal> | SET NO MAXVALUE
    | SET MINVALUE <signed numeric literal> | SET NO MINVALUE
    | SET CYCLE | SET NO CYCLE

<drop behavior> ::= CASCADE | RESTRICT

The <column definition>, <default option>, <table constraint definition>, <table period definition>, <partition bound> and <table option> are those of CREATE TABLE. One statement takes one action. ATTACH and DETACH are non-reserved words (extension).

ATTACH PARTITION makes a table a partition of the partitioned table, its rows checked against the bound, and DETACH PARTITION makes a partition a table of its own (extension). A table of a partitioning tree has its columns and periods altered by no action, and a partitioned table is given storage options and truncated through its partitions (Partitioned Tables).

DESCRIPTION

ALTER TABLE changes the definition of a base table.

ADD COLUMN adds a column, last. Each row has its default in it, or, of an identity column, its generator's next value, or, of a generated column, its expression's value. It takes the constraints written in its definition, and those of its domain. Each privilege held on the whole table, of SELECT, INSERT, UPDATE or REFERENCES, is held on the new column too, by the same grantee, with the same grant option.

ALTER COLUMN sets the column's default, or drops it. No row changes; the new default is taken by the rows inserted later.

Of an identity column, ALTER COLUMN changes its generator's options as ALTER SEQUENCE does, and SET GENERATED changes whether its values are always generated. RESTART starts the generator again at its start, or at the value written. A changed increment goes on from the generator's current base value. DROP IDENTITY makes it a column that takes values of its own, of no default, and drops its generator. Its NOT NULL constraint stays. Of a generated column, DROP EXPRESSION makes it a column that takes values of its own, keeping those it holds. No row changes.

ADD SCOPE gives a column of a reference type a scope, a typed table of its referenced type, so that -> and DEREF find the rows its values identify. DROP SCOPE removes it, with RESTRICT refused while a view, constraint or routine dereferences the column, and with CASCADE dropping those.

RENAME COLUMN (extension) gives a column a new name, its values, place, keys, indexes and privileges kept, and its rows not copied. A period it bounds and a table option that names it, such as COLUMNSTORE.ORDER, name it anew. A view, CHECK, assertion, trigger, routine or generation expression that reads the table and names the column, or its new name, refuses it, as does a view that selects * of the table: drop or change those first.

DROP COLUMN removes the column and its values; the columns after it move up one place. A table constraint that reads that column alone goes with it. RESTRICT refuses the drop if a view, an assertion, a foreign key of another table, or a constraint that reads other columns too refers to the column; CASCADE drops them with it. RESTRICT refuses it too if a generated column's expression references the column; CASCADE first drops each such generated column, as DROP COLUMN CASCADE would. A function or procedure whose body references the column is refused the same way, and dropped by CASCADE with what invokes it. If the drop then fails, the transaction can only roll back. The privileges on the column go.

ADD CONSTRAINT adds a table constraint, checked of the rows there now; a row that breaks it refuses it.

DROP CONSTRAINT drops a constraint of the table, its name written without a schema being of the session's default schema. RESTRICT refuses the drop of a UNIQUE or PRIMARY KEY constraint that a foreign key references; CASCADE drops that foreign key too.

ADD PERIOD FOR SYSTEM_TIME adds the table's system-time period with its two columns, written after it, of ROW START then ROW END. Each row there begins at the transaction timestamp and ends at the highest value of their type. DROP PERIOD FOR SYSTEM_TIME drops the period and both columns, as DROP COLUMN does under the behavior written. ADD SYSTEM VERSIONING makes a table of a system-time period system-versioned, and DROP SYSTEM VERSIONING makes it a base table again, its historical rows deleted. A view, constraint, assertion, routine or trigger that reads the table FOR SYSTEM_TIME refuses it under RESTRICT, and CASCADE drops each.

ADD PERIOD adds a period of two columns the table has, and its period constraint, CHECK (start < end), checked of the rows there now; a row whose start is not before its end refuses it. DROP PERIOD drops the period and its constraint. Under RESTRICT it is refused while a view, a CHECK, a domain constraint, an assertion, a routine or a trigger references the period in a period predicate, a UNIQUE or PRIMARY KEY is WITHOUT OVERLAPS of it, or a foreign key of this table or of another is of it; CASCADE drops them. A generated column whose expression references it refuses the drop under either. See CREATE TABLE.

A grouped query may read a column outside a set function because a key makes it dependent on the grouping columns. A view, a CHECK, a domain constraint, an assertion or a trigger whose query does so depends on that key, and on a NOT NULL that makes a UNIQUE's columns a key. RESTRICT refuses to drop such a constraint, and CASCADE drops each object that depends on it: a view with the views over it, a CHECK from its table, a domain constraint from its domain, an assertion, a trigger.

CREATE TABLE T (K INTEGER NOT NULL, N INTEGER, CONSTRAINT U_K UNIQUE (K));
CREATE VIEW V AS SELECT K, N FROM T GROUP BY K;
ALTER TABLE T DROP CONSTRAINT U_K RESTRICT;  -- refused, V depends on U_K
ALTER TABLE T DROP CONSTRAINT U_K CASCADE;   -- drops V too

SET and USING (extension) change how the table is stored: SET changes the options of its engine, DEFAULT an option's default; USING moves its rows to another engine, or to the same one made again, with the options WITH gives. INFORMATION_SCHEMA.TABLE_OPTIONS shows them. An option the engine can take in place changes no row; any other change copies the rows to storage made anew.

RESEAL (extension) copies the table's rows to storage made anew, as it is, and makes its indexes anew. Rows written before its option ENCODINGS changed keep the encodings, and the key, they were written with; copied, they pass through those ENCODINGS names now. Once the transaction commits and a checkpoint follows, nothing of the table is read with a key it named before, which may then be retired (Encryption). RESEAL is a non-reserved word: elsewhere, an identifier.

A view sees the columns its tables had when it was defined: a column added is not one of its.

ALTER TABLE runs within the session's transaction: ROLLBACK undoes it. Until the transaction ends, others read the table as it was; one that writes it is refused (SQLSTATE TB911), or, after SET LOCKS.WAIT = 'ON' (SET), waits for it.

RULES

  1. The table is a base table, not a view, and its user owns the table's schema.
  2. ADD COLUMN: no column of the table has the name. If its definition is NOT NULL and the table has rows, the column has a default.
  3. ALTER COLUMN and DROP COLUMN: the table has the column. DROP DEFAULT: the column has a default of its own. SET DEFAULT and DROP DEFAULT are not of an identity column, and SET DEFAULT is not of a generated column. SET GENERATED, the generator's options and DROP IDENTITY are of an identity column alone, and DROP EXPRESSION of a generated column alone. The options follow the rules of ALTER SEQUENCE. ADD COLUMN adds no second identity column.
  4. DROP COLUMN: the column is not the table's only one, nor one a table option names, such as COLUMNSTORE.ORDER (extension): the option is changed first.
  5. DROP CONSTRAINT: the table has the constraint. A constraint a column has from its domain is changed by ALTER DOMAIN. A period's constraint is dropped with its period alone, and no NOT NULL or PRIMARY KEY is dropped whose going leaves a period's column possibly nullable. DROP COLUMN drops no column of a period.
  6. ADD PERIOD: the table is a persistent base table, not a typed one, and has no period. No column is named as the period. Its columns are the table's, of one type, DATE or a timestamp type, a domain's over one among them, each known not nullable, and neither an identity nor a generated column. DROP PERIOD: the table has the period, nothing references it under RESTRICT, and no generation expression references it.
  7. ADD PERIOD FOR SYSTEM_TIME: the table is a persistent base table, not a typed one, has no system-time period and no column named SYSTEM_TIME, and adds a column of ROW START, the period's start, then one of ROW END, its end. ADD COLUMN adds no ROW START or ROW END column, and an application-time period adds no column. DROP PERIOD FOR SYSTEM_TIME: the table is not system-versioned.
  8. ADD SYSTEM VERSIONING: the table is a persistent base table, not a typed one, of a system-time period and no column of a reference type, and not system-versioned. DROP SYSTEM VERSIONING: it is, and under RESTRICT nothing reads it FOR SYSTEM_TIME.
  9. A system-versioned table's columns are not added, altered or dropped. A system-time period column has no default.
  10. A constraint added follows the rules of CREATE TABLE. A CHECK or a generation expression added calls a function the program registered only if the session's SQL.FUNCTION_CALLS is ON (extension). The table's constraints and generated columns made before keep their calls whatever it is.
  11. SET and USING: the engine is registered, and takes each option, of a value it accepts; an option of columns names the table's. A temporary table names no engine or options, and is not resealed.
  12. A temporary table is altered only while no other transaction has a session's rows of it open, and by a transaction that sees each session's rows: one begun after the last commit that wrote them. ADD and DROP COLUMN change each session's rows, its own and another's, the column added taking its default in each. Any other action changes no row and is every session's at once. The table's constraints, a constraint added among them, hold of each session's rows, read as that session sees the other temporary tables: its own rows of them.
  13. RENAME COLUMN: the table has the column, and no column or period of the new name. It is no typed table and not system-versioned, and nothing the RENAME COLUMN paragraph above lists names the column or the new name.
  14. No cursor of the transaction reads the table.
  15. The transaction is not READ ONLY.

PRIVILEGES

The table's schema is its user's. A column added, or a constraint, needs the USAGE and REFERENCES that CREATE TABLE says.

ERRORS

SQLSTATE SQLCODE When
2A000 -102 The action is not written as the synopsis shows
2A000 -103 A rule above is broken
3F000 -103 The schema of a constraint added does not exist
2A000 -104 Its user does not own the table's schema, or lacks a privilege a column or constraint needs
23000 -201 A row breaks the constraint added; a NOT NULL column added has no default, and the table has rows
23T01 -201 A row of a table attached is not one the bound holds (extension)
25000 -917 The transaction is READ ONLY
24000 -501 A cursor of the transaction reads the table
TB911 -911 Another transaction has a session's rows of a temporary table open, or committed rows of it after the transaction began; another transaction holds a lock on the table

EXAMPLES

Of a table STAFF (ID INTEGER PRIMARY KEY, NAME VARCHAR(30), SALARY DECIMAL(9,2)) of rows 1, Ada, 7200 and 2, Grace, 8100:

ALTER TABLE STAFF ADD COLUMN DEPT CHAR(3) DEFAULT 'ENG';
ALTER TABLE STAFF ALTER COLUMN DEPT SET DEFAULT 'OPS';
INSERT INTO STAFF (ID, NAME, SALARY) VALUES (3, 'Edsger', 6900);
ALTER TABLE STAFF ADD CONSTRAINT PAID CHECK (SALARY > 7000);
ALTER TABLE STAFF ADD CONSTRAINT PAID CHECK (SALARY > 0);
SELECT * FROM STAFF ORDER BY ID;
OK
OK
OK
turborel: SQLCODE -201 (SQLSTATE 23000): CHECK ANN.PAID of ANN.STAFF is false for a row [ISO/IEC 9075:1992 §11.6 GR3]
OK
           ID | NAME                           |      SALARY | DEPT
--------------+--------------------------------+-------------+------
            1 | Ada                            |     7200.00 | ENG
            2 | Grace                          |     8100.00 | ENG
            3 | Edsger                         |     6900.00 | OPS
(3 rows)

The rows there took the default DEPT had when it was added; the row inserted after, the default set later. The first PAID was refused, as Edsger's row breaks it.

PAID reads SALARY alone, so it goes with the column; a view that reads SALARY does not:

CREATE VIEW PAY AS SELECT NAME, SALARY FROM STAFF;
ALTER TABLE STAFF DROP COLUMN SALARY RESTRICT;
ALTER TABLE STAFF DROP COLUMN SALARY CASCADE;
SELECT * FROM PAY;
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): view ANN.PAY refers to ANN.STAFF.SALARY, so it is dropped only with CASCADE [ISO/IEC 9075:1992 §11.15 SR4]
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): table ANN.PAY is not defined [X3.135-1986 §5.4 SR5] (at character 14)

An identity column added to a table of rows numbers them, and is then restarted:

ALTER TABLE STAFF ADD COLUMN BADGE INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 500);
ALTER TABLE STAFF ALTER COLUMN BADGE RESTART WITH 900;
INSERT INTO STAFF (ID, NAME) VALUES (4, 'Barbara');
SELECT ID, BADGE FROM STAFF ORDER BY ID;
OK
OK
OK
           ID |        BADGE
--------------+--------------
            1 |          500
            2 |          501
            3 |          502
            4 |          900
(4 rows)

Its storage (extension):

ALTER TABLE STAFF SET (STATISTICS.BUCKETS = 50);
ALTER TABLE STAFF USING ROWSTORE WITH (OUT_OF_LINE = 200);
SELECT OPTION_NAME, OPTION_VALUE FROM INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE TABLE_NAME = 'STAFF' AND OPTION_NAME IN ('OUT_OF_LINE', 'STATISTICS.BUCKETS') ORDER BY 1;
OK
OK
 OPTION_NAME                                                  | OPTION_VALUE
--------------------------------------------------------------+--------------------------------------------------------------
 OUT_OF_LINE                                                  | 200
 STATISTICS.BUCKETS                                           | 50
(2 rows)

Its rows sealed again (extension), through the encodings its ENCODINGS names now:

ALTER TABLE STAFF RESEAL;
OK

Actions refused:

ALTER TABLE STAFF ADD COLUMN ID INTEGER;
ALTER TABLE STAFF ADD COLUMN HIRED DATE NOT NULL;
ALTER TABLE STAFF ALTER COLUMN NAME DROP DEFAULT;
ALTER TABLE NOPE ADD COLUMN X INTEGER;
ALTER TABLE STAFF DROP COLUMN DEPT;
turborel: SQLCODE -103 (SQLSTATE 2A000): ANN.STAFF already has a column ID [ISO/IEC 9075:1992 §11.4 SR1] (at character 29)
turborel: SQLCODE -201 (SQLSTATE 23000): ANN.STAFF has rows, in which NOT NULL column HIRED would be null [ISO/IEC 9075:1992 §11.11 GR2]
turborel: SQLCODE -103 (SQLSTATE 2A000): column NAME of ANN.STAFF has no default to drop [ISO/IEC 9075:1992 §11.14 SR2] (at character 31)
turborel: SQLCODE -103 (SQLSTATE 2A000): ANN.NOPE is not defined [ISO/IEC 9075:1992 §11.10 SR2] (at character 12)
turborel: SQLCODE -102 (SQLSTATE 2A000): expected CASCADE or RESTRICT, found end of statement (at character 34)

A column renamed, and a rename a view refuses:

ALTER TABLE STAFF RENAME COLUMN NAME TO FULL_NAME;
CREATE VIEW WHO AS SELECT FULL_NAME FROM STAFF;
ALTER TABLE STAFF RENAME COLUMN FULL_NAME TO GIVEN;
SELECT ID, FULL_NAME FROM STAFF WHERE ID = 1;
OK
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): view ANN.WHO names ANN.STAFF.FULL_NAME or GIVEN in its text, so the column is renamed only once nothing does (at character 32)
           ID | FULL_NAME
--------------+--------------------------------
            1 | Ada
(1 row)

STANDARD

ISO/IEC 9075:1992 §11.10 to §11.17. A column dropped takes a table constraint that reads it alone, even with RESTRICT [§11.15 SR4]. The actions of an identity column and DROP EXPRESSION are ISO/IEC 9075-2:2023 §11.20 to §11.22, Features T178, F384, F385 and F386, and the drop of a column a generation expression references is §11.23 SR7f and GR3. ADD PERIOD and DROP PERIOD are §11.27 and §11.28, Features T181 and T502, and what a period keeps of its columns §11.23 SR6 and §11.26 SR10. ADD and DROP SYSTEM VERSIONING are §11.29 and §11.30, Feature T180, the start given existing rows by ADD PERIOD FOR SYSTEM_TIME the transaction timestamp (IV181). SET, USING, RESEAL, RENAME COLUMN, ATTACH PARTITION and DETACH PARTITION are extensions; SQL:2023 renames no column. SQL-86 has no ALTER TABLE.

SEE ALSO

CREATE TABLE, DROP TABLE, ALTER DOMAIN, ALTER SEQUENCE, CREATE VIEW, Temporary Tables, Partitioned Tables