ALTER TABLE

The ALTER TABLE statement allows you to modify a table in a variety of ways, including adding and dropping columns and constraints from the table.

In this release, you cannot use ALTER TABLE to:
  • add a primary key
  • drop a foreign key constraint

Syntax

ALTER TABLE table-Name
   {
   ADD COLUMN column-definition |
   ADD CONSTRAINT clause |
   DROP [ COLUMN ] column-name
   DROP { UNIQUE constraint-name |
          CHECK constraint-name
   }
   ALTER [ COLUMN ] column-alteration
   }

column-definition

Simple-column-name [ DataType ]
  [ Column-level-constraint ]*
  [ [ WITH ] DEFAULT DefaultConstantExpression
    | generation-clause
  ]

The syntax for the column-definition for a new column is a subset of the syntax for a column in a  CREATE TABLE statement.

The DataType can be omitted only if you specify a generation-clause. If you omit the DataType, the type of the generated column is the type of the generation-clause. If you specify both a DataType and a generation-clause, the type of the generation-clause must be assignable to DataType.

column-alteration

column-Name SET DATA TYPE VARCHAR(integer) |
column-name SET INCREMENT BY integer-constant |
column-name RESTART WITH integer-constant |
column-name [ NOT ] NULL |
column-name [ WITH | SET ] DEFAULT default-value |
column-name DROP DEFAULT

In the column-alteration, SET INCREMENT BY integer-constant specifies the interval between consecutive values of the identity column. The next value to be generated for the identity column will be determined from the last assigned value with the increment applied. The column must already be defined with the IDENTITY attribute.

RESTART WITH integer-constant specifies the next value to be generated for the identity column. RESTART WITH is useful for a table that has an identity column that was defined as GENERATED BY DEFAULTand that has a unique key defined on that identity column.

Because GENERATED BY DEFAULT allows both manual inserts and system generated values, it is possible that manually inserted values can conflict with system generated values. To work around such conflicts, use the RESTART WITH syntax to specify the next value that will be generated for the identity column.

Consider the following example, which involves a combination of automatically generated data and manually inserted data:

CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY,
	               k INT)
	 CREATE UNIQUE INDEX tautoInd ON tauto(i)
	 INSERT INTO tauto(k) values 1,2;

The system will automatically generate values for the identity column. But now you need to manually insert some data into the identity column:

INSERT INTO tauto VALUES (3,3);
INSERT INTO tauto VALUES (4,4);
INSERT INTO tauto VALUES (5,5);

The identity column has used values 1 through 5 at this point. If you now want the system to generate a value, the system will generate a 3, which will result in a unique key exception because the value 3 has already been manually inserted. To compensate for the manual inserts, issue an ALTER TABLE statement for the identity column with RESTART WITH 6:

ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6;

ALTER TABLE does not affect any view that references the table being altered. This includes views that have a wildcard asterisk (*) in their SELECT list. You must drop and re-create those views if you wish them to return the new columns.

To change a column constraint to NOT NULL, there has to be a valid value for the column.

Splice Machine raises an error if you try to change the DataType of a generated column to a type which is not assignable from the type of the generation-clause. Splice Machine also raises an error if you try to add a DEFAULT clause to a generated column.

Usage

The ALTER TABLE statement allows you to:

  • add a column to a table
  • add a constraint to a table
  • drop a column from a table
  • drop an existing constraint from a table*
  • increase the width of a VARCHAR column
  • change the increment value and start value of the identity column
  • change the nullability constraint for a column
  • change the default value for a column

Adding columns

The syntax for the column-definition for a new column is almost the same as for a column in a CREATE TABLE statement. This syntax allows a column constraint to be placed on the new column within the ALTER TABLE ADD COLUMN statement. However, a column with a NOT NULL constraint can be added to an existing table if you give a default value; otherwise, an exception is thrown when the ALTER TABLE statement is executed.

If a table has an UPDATE trigger without an explicit column list, adding a column to that table in effect adds that column to the implicit update column list upon which the trigger is defined, and all references to transition variables are invalidated so that they pick up the new column.

Adding constraints

ALTER TABLE ADD CONSTRAINT adds a table-level constraint to an existing table.

The ALTER TABLE ADD CONSTRAINT statement is not currently taking currently running transactions into account, and thus can fail to add the constraint. This issue will be resolved in a future release.

You can reliably add constraints when using the CREATE TABLE statement.

The following limitations exist on adding a constraint to an existing table:

  • When adding a check constraint to an existing table, Splice Machine checks the table to make sure existing rows satisfy the constraint. If any row is invalid, Splice Machine throws a statement exception and the constraint is not added.

For information on the syntax of constraints, see CONSTRAINT clause. Use the syntax for table-level constraint when adding a constraint with the ADD TABLE ADD CONSTRAINT syntax.

Dropping columns

ALTER TABLE DROP COLUMN allows you to drop a column from a table.

The keyword COLUMN is optional.

You may not drop the last (only) column in a table.

Modifying columns

The column-alteration allows you to alter the named column in the following ways:

  • Increasing the width of an existing VARCHAR column. CHARACTER VARYING or CHAR VARYING can be used as synonyms for the VARCHAR keyword.

    To increase the width of a column of these types, specify the data type and new size after the column name.

    You are not allowed to decrease the width or to change the data type. You are not allowed to increase the width of a column that is part of a primary or unique key referenced by a foreign key constraint or that is part of a foreign key constraint.

  • Specifying the interval between consecutive values of the identity column.

    To set an interval between consecutive values of the identity column, specify the integer-constant. You must previously define the column with the IDENTITY attribute (SQLSTATE 42837). If there are existing rows in the table, the values in the column for which the SET INCREMENT default was added do not change.

  • Modifying the nullability constraint of a column.

    You can add the NOT NULL constraint to an existing column; however, you cannot do so if there are NULL values for the column in the table.

    You can remove the NOT NULL constraint from an existing column; however, you cannot do so if the column is used in a PRIMARY KEY constraint.

  • Changing the default value for a column.

    You can use DEFAULT default-value to change a column default. To disable a previously set default, use DROP DEFAULT (alternatively, you can specify NULL as the default-value).

Setting defaults

You can specify a default value for a new column. A default value is the value that is inserted into a column if no other value is specified. If not explicitly specified, the default value of a column is NULL. If you add a default to a new column, existing rows in the table gain the default value in the new column.

For more information about defaults, see  CREATE TABLE statement.

An ALTER TABLE statement causes all statements that are dependent on the table being altered to be recompiled before their next execution.

Examples

This section provides examples of using the ALTER TABLE statement.

Example 1: Adding Columns to a Table

In this example. we create a new table, and then use ALTER TABLE statements to add three columns that we have decided to include:

splice> CREATE TABLE PlayerTrades (
   ID INT NOT NULL,
   PlayerName VARCHAR(32),
   Position CHAR(2),
   OldTeam VARCHAR(32),
   NewTeam VARCHAR(32) );
0 rows inserted/updated/deleted

splice> ALTER TABLE PlayerTrades ADD COLUMN Updated TIMESTAMP;
0 rows inserted/updated/deleted

splice> ALTER TABLE PlayerTrades ADD COLUMN TradeDate DATE;
0 rows inserted/updated/deleted

splice> ALTER TABLE PlayerTrades ADD COLUMN Years INT;
0 rows inserted/updated/deleted

splice> INSERT INTO PlayerTrades VALUES( 1, 'Greinke', 'SP', 'Dodgers', 'Giants', CURRENT_TIMESTAMP, CURRENT_DATE);
1 row inserted/updated/deleted

splice> DESCRIBE PlayerTrades;
COLUMN_NAME   |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
---------------------------------------------------------------------------
ID            |INTEGER  |0   |10  |10    |NULL      |NULL      |NO
PLAYERNAME    |VARCHAR  |NULL|NULL|32    |NULL      |64        |YES
POSITION      |CHAR     |NULL|NULL|2     |NULL      |4         |YES
OLDTEAM       |VARCHAR  |NULL|NULL|32    |NULL      |64        |YES
NEWTEAM       |VARCHAR  |NULL|NULL|32    |NULL      |64        |YES
UPDATED       |TIMESTAMP|9   |10  |29    |NULL      |NULL      |YES
TRADEDATE     |DATE     |0   |10  |10    |NULL      |NULL      |YES
YEARS         |INTEGER  |0   |10  |10    |NULL      |NULL      |YES

8 rows selected

Example 2: Altering Columns

In this example, we use ALTER TABLE to alter columns in various ways:

  • specify that the Updated column cannot be NULL

  • set the default value for Years to 3

  • set the default value for NewTeam to 'Giants'

splice> ALTER TABLE PlayerTrades ALTER COLUMN Updated NOT NULL;
0 rows inserted/updated/deleted

splice> ALTER TABLE PlayerTrades ALTER COLUMN Years DEFAULT 3;
0 rows inserted/updated/deleted

splice> ALTER TABLE PlayerTrades ALTER COLUMN NewTeam DEFAULT 'Giants';
0 rows inserted/updated/deleted

splice> DESCRIBE PlayerTrades;
COLUMN_NAME   |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
---------------------------------------------------------------------------
ID            |INTEGER  |0   |10  |10    |NULL      |NULL      |NO
PLAYERNAME    |VARCHAR  |NULL|NULL|32    |NULL      |64        |YES
POSITION      |CHAR     |NULL|NULL|2     |NULL      |4         |YES
OLDTEAM       |VARCHAR  |NULL|NULL|32    |NULL      |64        |YES
NEWTEAM       |VARCHAR  |NULL|NULL|32    |'Giants'  |64        |YES
UPDATED       |TIMESTAMP|9   |10  |29    |NULL      |NULL      |NO
TRADEDATE     |DATE     |0   |10  |10    |NULL      |NULL      |YES
YEARS         |INTEGER  |0   |10  |10    |3         |NULL      |YES

7 rows selected

Example 3: Dropping a column

This example drops the Years column from our table, and then drops the default associated with NewTeam:

splice> ALTER TABLE PlayerTrades DROP COLUMN Years;
0 rows inserted/updated/deleted

splice> ALTER TABLE PlayerTrades ALTER COLUMN NewTeam DROP DEFAULT;
0 rows inserted/updated/deleted

splice> DESCRIBE PlayerTrades;
COLUMN_NAME   |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
---------------------------------------------------------------------------
ID            |INTEGER  |0   |10  |10    |NULL      |NULL      |NO
PLAYERNAME    |VARCHAR  |NULL|NULL|32    |NULL      |64        |YES
POSITION      |CHAR     |NULL|NULL|2     |NULL      |4         |YES
OLDTEAM       |VARCHAR  |NULL|NULL|32    |NULL      |64        |YES
NEWTEAM       |VARCHAR  |NULL|NULL|32    |NULL      |64        |YES
UPDATED       |TIMESTAMP|9   |10  |29    |NULL      |NULL      |NO
TRADEDATE     |DATE     |0   |10  |10    |NULL      |NULL      |YES

7 rows selected

Example 4: Changing Varchar Column Width

This example changes the width of one of our VARCHAR columns:

splice> ALTER TABLE PlayerTrades ALTER COLUMN PlayerName SET DATA TYPE VARCHAR(40);
0 rows inserted/updated/deleted

splice> DESCRIBE PlayerTrades;
COLUMN_NAME   |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
---------------------------------------------------------------------------
ID            |INTEGER  |0   |10  |10    |NULL      |NULL      |NO
PLAYERNAME    |VARCHAR  |NULL|NULL|40    |NULL      |80        |YES
POSITION      |CHAR     |NULL|NULL|2     |NULL      |4         |YES
OLDTEAM       |VARCHAR  |NULL|NULL|32    |NULL      |64        |YES
NEWTEAM       |VARCHAR  |NULL|NULL|32    |NULL      |64        |YES
UPDATED       |TIMESTAMP|9   |10  |29    |NULL      |NULL      |NO
TRADEDATE     |DATE     |0   |10  |10    |NULL      |NULL      |YES

7 rows selected

Example 5: Changing Increment Value

This example shows creating a table with an identity column, and then changing the increment for that column:

splice> CREATE TABLE NewPlayers( 
   newID INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   PlayerName);
0 rows inserted/updated/deleted

splice> ALTER TABLE NewPlayers ALTER COLUMN newID SET INCREMENT BY 10;
0 rows inserted/updated/deleted

splice> INSERT INTO NewPlayers(PlayerName) ('Greinke'),('Cespedes');
2 rows inserted/updated/deleted

splice> SELECT * FROM NewPlayers;
NEWID      |PLAYERNAME
-----------------------
1          |Greinke
11         |Cespedes

2 rows selected

See Also