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.

Syntax

ALTER TABLE table-Name
   {
   ADD COLUMN column-definition |
   ADD CONSTRAINT clause |
   DROP [ COLUMN ] column-name
   DROP { constraint-name |
          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-name

An SQLIdentifier specifying the name of the column that you want to drop.

constraint-name

An SQLIdentifier specifying the name of the constraint that you want to drop.

If the constraint is unnamed, you can specify the generated CONSTRAINTNAME ID that is stored in the   SYS.SYSCONSTRAINTS table as a delimited value. You can find the constraint ID by joining SYS.SYSCONSTRAINTS with   SYS.SYSTABLES.

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.

Dropping constraints

ALTER TABLE DROP CONSTRAINT allows you to drop a constraint from a table. You can specify the constraint by its name, or if the constraint is unnamed, you can specify the generated CONSTRAINTNAME ID that is stored in the   SYS.SYSCONSTRAINTS table as a delimited value. You can find the constraint ID by joining SYS.SYSCONSTRAINTS with   SYS.SYSTABLES.

Example 8: Dropping a Foreign Key Constraint at the end of this topic shows dropping an unnamed foreign key constraint.

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

Example 6: Adding a Foreign Key After Table Creation

This example shows adding a foreign key into a table that already contains data, and then attempting an invalid insertion to demonstrate that the new constraint is active:

CREATE TABLE orders (id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(30));
CREATE TABLE orderlines (lineitemid INTEGER NOT NULL PRIMARY KEY, orderid INTEGER, total DOUBLE);

INSERT INTO orders VALUES (1,'test1');
INSERT INTO orders VALUES (2, 'test2');
INSERT INTO orders VALUES (3, 'test3');

INSERT INTO orderlines VALUES (1,1,12.50);
INSERT INTO orderlines VALUES (2,1,12.50);

ALTER TABLE orderlines ADD CONSTRAINT FK_Order FOREIGN KEY (orderid) REFERENCES Orders(id);

    # This insertion fails because of the newly added foreign key constraint:
splice> INSERT INTO ORDERLINES VALUES (3,5,12.50);
ERROR 23503: Operation on table 'ORDERLINES' caused a violation of foreign key constraint 'FK_ORDER' for key (ORDERID).  The statement has been rolled back.

Example 7: Adding a Foreign Key in Table With Invalid Data

This example shows an attempt to add a foreign key into a table that already contains data that does not conform to the new constraint:

CREATE TABLE orders3 (id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(30));
CREATE TABLE orderlines3 (lineitemid INTEGER NOT NULL PRIMARY KEY, orderid INTEGER, total DOUBLE);

INSERT INTO orders3 VALUES (1,'test1');
INSERT INTO orders3 VALUES (2, 'test2');
INSERT INTO orders3 VALUES (3, 'test3');

INSERT INTO orderlines3 VALUES (1,1,12.50);
INSERT INTO orderlines3 VALUES (2,1,12.50);
INSERT INTO orderlines3 VALUES (3,5,12.50);

    # Creating this foreign key fails because the table contains non-conformant data:
splice> ALTER TABLE orderlines3 ADD CONSTRAINT FK_Order3 FOREIGN KEY (orderid) REFERENCES Orders3(id);
ERROR X0Y45: Foreign key constraint 'FK_ORDER3' cannot be added to or enabled on table "SPLICE"."ORDERLINES3" because one or more foreign keys do not have matching referenced keys.

Example 8: Dropping a Foreign Key Constraint

This example shows an attempt to add a foreign key into a table that already contains data that does not conform to the new constraint, and then after dropping the constraint. Since the constraint is unnamed, we can specify its ID, which you can find by joining SYS.SYSCONSTRAINTS with SYS.SYSTABLES. In this example, an earlier error message shows us the ID, so we don’t need to look it up:

-- create original table
splice> CREATE TABLE t1 (c1 NUMERIC PRIMARY KEY);
0 rows inserted/updated/deleted

-- create secondary table
CREATE TABLE t2 (
   c1 NUMERIC PRIMARY KEY,
   c2 NUMERIC REFERENCES t1(c1) );
0 rows inserted/updated/deleted

-- try (but fail) to enter a row because of fk constraint:
splice> insert into t2 values (1,1);
ERROR 23503: Operation on table 'T2' caused a violation of foreign key constraint 'SQL180830233242711' for key (C2).  The statement has been rolled back.

-- enter record correctly:
splice> insert into t1 values 1;
1 row inserted/updated/deleted

splice> insert into t2 values (1,1);
1 row inserted/updated/deleted

-- now get rid of the fk constraint:
splice> alter table t2 drop constraint SQL180830233242711;
0 rows inserted/updated/deleted

-- now we can insert rows that don't reference an fk:
splice> insert into t2 values (2,2);
1 row inserted/updated/deleted

See Also