ALTER TABLE

SQL Guide : solidDB® SQL statements : ALTER TABLE

ALTER TABLE

ALTER TABLE base_table_name
{
   ADD [COLUMN] column_identifier data_type
    [DEFAULT literal | NULL] [NOT NULL] |
   ADD CONSTRAINT constraint_name dynamic_table_constraint |
   DROP CONSTRAINT constraint_name |
   ALTER [ COLUMN ] column_name
  {DROP DEFAULT | {SET DEFAULT literal | NULL} } |
  {{ADD | DROP} NOT NULL }
     DROP [COLUMN] column_identifier |
   RENAME [COLUMN]
         column_identifier column_identifier |
     MODIFY [COLUMN] column_identifier data-type |
   MODIFY SCHEMA schema_name} |
   SET HISTORY COLUMNS (c1, c2, c3) |
   SET {OPTIMISTIC | PESSIMISTIC} |
   SET STORE {DISK | MEMORY} |
   SET [NO]SYNCHISTORY |
   SET TABLE NAME new_base_table_name
   SET POOLSIZE percentage
   ADD CACHE SEGMENT segment_name [WHERE range_specification]
   DROP CACHE SEGMENT segment_name
}

where

dynamic_table_constraint::=
{FOREIGN KEY (column_identifier [, column_identifier] ...)
REFERENCES table_name [(column_identifier
[, column_identifier] ] ...)}
[referential_triggered_action] |
CHECK (check_condition) |
UNIQUE (column_identifier)

referential_triggered_action::=
ON {UPDATE | DELETE} {CASCADE | SET NULL | SET DEFAULT |
RESTRICT |NO ACTION}

range_specification ::= <column> < <value>
                        <column> > <value>
                        <column> = <value>
                        <column> <> <value>
                        <column> BETWEEN <value> AND <value>
                        <column> OLDERTHAN <value> DAYS
                        <column> OLDERTHAN <value> SECONDS
                        <column> NEWERTHAN <value> DAYS
                        <column> NEWERTHAN <value> SECONDS

Usage

You can use the ALTER TABLE statement for the following:

▪Modify the structure of a table; add, remove, modify, and rename columns.

Note A column cannot be dropped if it is part of a unique or primary key.

▪Modify the column size.

You can change the width of a column using the ALTER TABLE command. You can increase the column width at any time (that is, whether a table is empty [no rows] or non-empty). You cannot decrease the column width when the table is non-empty; a table must be empty to decrease the column width.

For supported data types and column size restrictions, see Data types.

▪Set tables to use optimistic or pessimistic concurrency control.

Disk-based tables (D-tables) can be set to optimistic or pessimistic with the statement ALTER TABLE base_table_name SET {OPTIMISTIC | PESSIMISTIC}. By default, all D-tables are optimistic. M-tables are always pessimistic. A database-wide default for D-tables can be set with the parameter General.Pessimistic.

▪Change the schema and owner for the table.

The owner of a table can be changed using the ALTER TABLE base_table_name MODIFY SCHEMA schema_name statement. This statement gives all rights, including creator rights, to the new owner. The old owner's access rights to the table, excluding the creator rights, are preserved.

▪Change whether the table is stored in memory or on disk.

A table may be changed from disk-based to in-memory based or vice-versa. This may be done only if the table is empty. If you try to change a table to the samestorage mode that it already uses (for example, if you try to change an in-memory table to use in-memory storage), then the command has no effect, and no error message is issued.

▪Change the referential constraint settings.

For more information, see Referential integrity.

▪Define synchronization setting for tables.

For more information, see ALTER TABLE ... SET HISTORY COLUMNS and ALTER TABLE ... SET SYNCHISTORY.

▪Work with cache segments:

– Change cache segment size (SET POOLSIZE percentage).

Note You must restart the server to make the cache segment size change effective immediately. If you do not restart the server, the cache segment size change is gradual.

– Assign a table (ADD CACHE SEGMENT) or a part of a table (ADD CACHE SEGMENT ... WHERE) to a cache segment.

When defining a data range, the OLDERTHAN and NEWERTHAN keywords specify date and time ranges; the data type used must be of type DATE or TIMESTAMP.

If the value cannot be converted to the column type, the operation fails.

– Drop a table from a cache segment (DROP CACHE SEGMENT).

Examples

ALTER TABLE table1 ADD x INTEGER;

ALTER TABLE table1 RENAME COLUMN old_name new_name;

ALTER TABLE table1 MODIFY COLUMN xyz SMALLINT;

ALTER TABLE table1 DROP COLUMN xyz;

ALTER TABLE table1 SET STORE MEMORY;

ALTER TABLE table1 SET PESSIMISTIC;

ALTER TABLE table2 ADD COLUMN col_new CHAR(8) DEFAULT ’VACANT’ NOT NULL;

ALTER TABLE table2 ALTER COLUMN col_new SET DEFAULT ’EMPTY’;

ALTER TABLE table2 ALTER COLUMN col_new DROP DEFAULT;

ALTER TABLE dept_tabl ADD CONSTRAINT div_check CHECK(division_id < 12);

ALTER TABLE dept_tabl DROP CONSTRAINT div_check;

ALTER TABLE HISTORY_DATA ADD CACHE SEGMENT HISTORY_CACHE

ALTER TABLE HISTORY_DATA ADD CACHE SEGMENT HISTORY_CACHE WHERE DT OLDERTHAN 7 DAYS

Related reference

CREATE TABLE

CREATE CACHE SEGMENT

ALTER TABLE ... SET HISTORY COLUMNS

ALTER TABLE ... SET SYNCHISTORY