Appearance

Reference>Meta Database>SQL Command Reference>General DDL>ALTER TABLE

ALTER TABLE ​

Using ALTER TABLE, you can modify the properties, columns, constraints, etc., for an existing table.

See also sections CREATE TABLE and DROP TABLE.

Syntax ​

sql
ALTER TABLE folder_name.table_name
 [ alter_table_props
              | column_clause
 ]
;

Parameters ​

folder_name: The name of your folder.

table_name: The name of the table to be altered.

alter_table_props: Alters a table. See alter_table_props-Details details.

column_clause: This clause contains subclauses for adding, deleting, or otherwise modifying a column. See column_clause-Details.

Table of Examples ​

alter_table_props-Details ​

This clause is used:

Syntax ​

sql
{ 
  RENAME TO new_table_name
}

Parameters ​

RENAME TO new_table_name: This clause allows renaming the table to new_table_name. See also Example ALTER TABLE ... RENAME TO.

sql
ALTER TABLE mkw_doku.my_table1 RENAME TO mkw_doku.my_table_ren;

Table 'mkw_doku.my_table1' was altered

column_clause-Details ​

This clause is used:

Syntax ​

sql
{ add_column_clause
  | modify_column_clause
  | drop_column_clause
  }...
| rename_column_clause

Parameters ​

ADD col_def: This clause adds a column to a table. The col_def clause specifies column definitions for the new or modified column. See col_def-Klausel Details.

Restrictions

A column with a NOT NULL constraint cannot be added to a table if the table already contains rows. To add a new column with a NOT NULL constraint to a table that already contains rows, the DEFAULT clause must be used.

add_column_clause: This clause allows add new columns to a table. See add_column_clause-Details.

modify_column_clause: This clause allows modifying the properties of an existing column. See modify_column_clause-Details.

drop_column_clause: This clause deletes columns from a table. See drop_column_clause-Details.

rename_column_clause: This clause renames a column in the table. The new column name must not be identical to any other column name in the table. See rename_column_clause-Details.

add_column_clause-Details ​

This clause is used:

The clause allows to add new columns to a table.

Syntax ​

sql
{ ADD
   {col_def} | ({col_def} [, {col_def}] ...)
}

Parameters ​

ADD: This clause can be used to add new columns to a table.

col_def-Details ​

This clause is used:

Syntax ​

sql
col_name [datatype]
  [DEFAULT [ON NULL] expr]
  ]

Parameters ​

col_name: The name of the column.

datatype: The data type. See also SQL Datatype Reference.

DEFAULT expr: This clause specifies a default value for a new column or a new default value for an existing column. This value is assigned to the column's rows when a subsequent INSERT statement does not specify a value for the column.

The data type of expr must match the data type specified for the column, and the column must be wide enough to accommodate this expression. The expr can include any SQL function, except when the return value of the function is a literal argument or a column reference. Return values of such types are not supported as arguments for expr.

Restrictions for DEFAULT clause

  • The value of the DEFAULT clause is stored in the metadata. The column itself is not filled with data. However, subsequent queries that specify new columns will be rewritten so that the default value is returned in the result set.
  • A scalar subquery expression is not supported as a form of expression. Otherwise, an expression can take any form.

ON NULL expr: This clause causes the value expr to be assigned instead when an INSERT statement attempts to assign a value to a row in the affected column that evaluates to NULL. When ON NULL is specified, the constraint states NOT NULL and NOT DEFERRABLE are implicitly specified, which will result in an error if an inline constraint (conflicting with NOT NULL and NOT DEFERRABLE) is specified.

drop_column_clause-Details ​

This clause is used:

The clause allows freeing up space in the database by deleting columns that are no longer needed. See also Example 4: DROP COLUMNS.

Syntax ​

sql
{ DROP {COLUMN col_name
       | (col_name [, col_name]...)
       }
}

Parameters ​

DROP: This clause can be used to remove the column identifier and the data associated with the target column from each row of the table.

When data is deleted:

  • All indexes defined on any of the target columns are also deleted.
  • All constraints referencing a target column are removed.

COLUMN col_name or (col_name1, col_name2): The name of a column (columns) to be deleted (deleted).

COLUMN: The keyword is used to signal to the database that only a column name follows.

If it is a list of columns, they are specified in parentheses, and the keyword COLUMN is omitted.

The list of column names must not contain duplicates.

modify_column_clause-Details ​

This clause is used:

Syntax ​

sql
MODIFY
{(modify_col_properties 
    [, modify_col_properties]...)
}

Parameters ​

modify_col_properties: This clause allows modifying the properties of a column. All column definitions omitted in this clause remain unchanged (e.g., data types, default values, or constraints). See modify_col_properties-Details.

modify_col_properties-Details ​

This clause is used:

Syntax ​

sql
col_name [datatype]
       [DEFAULT [ON NULL] expr]

Parameters ​

col_name: The name of the column to be modified.

datatype: The data type of each column can only be changed if all rows in the column contain NULL. See also SQL Datatype Reference.

DEFAULT [ON NULL] expr: See 'DEFAULT [ON NULL] expr'.

rename_column_clause-Details ​

This clause is used:

This clause allows renaming a column in the table. The new column name must not be identical to any other column name in the table.

Syntax ​

sql
RENAME COLUMN old_name TO new_name

Usage Notes ​

Dependent views, functions, and procedures become invalid. The database will attempt to revalidate them on the next access, and these objects may need to be modified with the new column name if revalidation fails.

Restrictions:

This clause cannot be combined with any other column_clauses clause in the same statement.

Examples ​

Example 1: ALTER TABLE ... RENAME TO ​

Please create this example table if you want to run the examples yourself (please expand)...
sql
CREATE TABLE my_folder.planets (id INTEGER, name STRING);
INSERT INTO my_folder.planets (id, name) VALUES (1,'Jupiter');
INSERT INTO my_folder.planets (id, name) VALUES (2,'Saturn');
INSERT INTO my_folder.planets (id, name) VALUES (3,'Uranus');
INSERT INTO my_folder.planets (id, name) VALUES (4,'Earth');
INSERT INTO my_folder.planets (id, name) VALUES (5,'Venus');
INSERT INTO my_folder.planets (id, name) VALUES (6,'Mars');
INSERT INTO my_folder.planets (id, name) VALUES (7,'Neptune');
INSERT INTO my_folder.planets (id, name) VALUES (8,'Mercury');
sql
SELECT * FROM my_folder.planets;
txt
+----+------------+
| ID | NAME       |
+----+------------+
| 1  | Jupiter    |
| 2  | Saturn     |
| 3  | Uranus     |
| 4  | Earth      |
| 5  | Venus      |
| 6  | Mars       |
| 7  | Neptune    |
| 8  | Mercury    |
+----+------------+
sql
ALTER TABLE my_folder.planets RENAME TO my_folder.planets_ren;
SELECT * FROM my_folder.planets;
txt
CompileError: Table or View 'my_folder.planets' does not exist!
sql
SELECT * FROM my_folder.planets_ren;
txt
+----+------------+
| ID | NAME       |
+----+------------+
| 1  | Jupiter    |
| 2  | Saturn     |
| 3  | Uranus     |
| 4  | Earth      |
| 5  | Venus      |
| 6  | Mars       |
| 7  | Neptune    |
| 8  | Mercury    |
+----+------------+

Example 2: DROP COLUMNS ​

sql
CREATE TABLE my_folder.my_table ( col1 INTEGER, 
                                  col2 INTEGER, 
                                  col3 INTEGER); 
INSERT INTO my_folder.my_table(col1, col2, col3) VALUES(3,4,5);
INSERT INTO my_folder.my_table(col1, col2, col3) VALUES(13,14,15);
SELECT * FROM my_folder.my_table;
txt
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
| 3    | 4    | 5    |
| 13   | 14   | 15   |
+------+------+------+
sql
ALTER TABLE my_folder.my_table DROP COLUMN col2;
SELECT * FROM my_folder.my_table;
txt
+------+------+
| COL1 | COL3 | 
+------+------+
| 3    | 5    |
| 13   | 15   |
+------+------+
© 2025 MetaKraftwerk GmbH. All Rights Reserved.