ALTER TABLE

solidDB Help : solidDB reference : SQL: Statements : ALTER TABLE

ALTER TABLE

Note Check the solidDB Release Notes for any limitations that are associated with using this statement with a grid in the current release.

ALTER TABLE table‑name {
   ADD [COLUMN] col‑name data‑type [DEFAULT {literal | NULL}] [NOT NULL] |
   ALTER [COLUMN] col‑name {DROP DEFAULT | SET DEFAULT {literal | NULL} |
{ADD | DROP} NOT NULL} |
   DROP [COLUMN] col‑name |
   RENAME [COLUMN] old‑col‑name new‑col‑name |
   MODIFY [COLUMN] col‑name data‑type |
   MODIFY SCHEMA schema‑name} |
   SET TABLE NAME new‑table‑name |
   ADD CONSTRAINT constraint‑name dynamic‑table‑constraint |
   DROP CONSTRAINT constraint‑name |
   SET STORE {DISK | MEMORY} |
   SET {OPTIMISTIC | PESSIMISTIC} |
   SET [NO]SYNCHISTORY |
   SET HISTORY COLUMNS (col‑name [, col‑name …]) |
   ADD CACHE SEGMENT segment‑name [WHERE range‑specification] |
   DROP CACHE SEGMENT segment‑name |
   SET POOLSIZE percentage |
   SET {COMPRESSED | NONCOMPRESSED}}

where:

dynamic‑table‑constraint::={
   FOREIGN KEY (col‑name [, col‑name …]) REFERENCES table‑name [(col‑name [, col‑name …]) [referential‑triggered‑action] |
   CHECK (check‑condition) |
   UNIQUE (col‑id)}

referential‑triggered‑action::= ON {UPDATE | DELETE}
{CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}

check‑condition (see check-condition)

data‑type (see data-type)

Access requirements

Owner of table or SYS_ADMIN_ROLE role

Usage

Use the ALTER TABLE statement to execute the following actions:

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

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

You change the width of a column by using the ALTER TABLE...MODIFY statement. You can increase the column width at any time (that is, whether a table is empty [no rows] or non-empty), however, a table must be empty in order for you to decrease the column width.

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

▪ Change the schema and owner for the table.

Change the owner of a table by using the ALTER TABLE ... MODIFY SCHEMA statement. This statement gives all rights, including creator rights, to the new owner. The old owner access rights to the table, excluding the creator rights, are preserved.

▪ Change the name of a table by using the ALTER TABLE .., SET TABLE NAME statement

▪ Change the referential constraint settings.

For details of the referential‑triggered‑action, see CREATE TABLE.

Manage constraints dynamically by using the following sub-clauses of the ALTER TABLE statement:

– ADD CONSTRAINT: This clause adds a named constraint to a table.

– DROP CONSTRAINT: This clause removes a named constraint from a table.

In solidDB, when the keyword CONSTRAINT is used, the constraint name is mandatory.

– CHECK: This constraint allows you to specify rules to your tables or table columns. Each rule is a condition that must not be false for any row in the table on which it is defined. Otherwise the table cannot be updated.

The rules are Boolean expressions. The rule can check, for example, a range of values, equity, or the rule can be a simple comparison. You can use several checks in one statement. The following expressions and operators are available:

Expression	Explanation
<	less than
>	greater than
—	equal to
<=	less than or equal to
>=	greater than or equal to
<>	not equal to
AND	conjunction
ANY	in the list that follows or in the table specified
BETWEEN	between
IN	in the list that follows or in the table specified
MAX	maximum value
MIN	minimum value
NOT	negation
OR	disjunction
XOR	exclusive or

– UNIQUE. The UNIQUE constraint requires that no two rows in a table contain the same value in a given column or list of columns. You can create a unique constraint at either the table level or the column level.

Note Primary keys contain the unique constraint.

– FOREIGN KEY. The FOREIGN KEY constraint requires that each value in the foreign key column must have a matching value in the referenced table.

Note solidDB automatically generates names for unnamed constraints. If you want to view the names, use the command soldd -x hiddennames.

▪ Change whether the table is stored in memory or on disk. For more information, see ALTER TABLE ... SET STORE DISK or MEMORY.

▪ Set tables to use optimistic or pessimistic concurrency control. For more information, see ALTER TABLE ... SET OPTIMISTIC or PESSIMISTIC.

▪ Work with cache segments. For more information, see ALTER TABLE ... ADD or DROP CACHE SEGMENT and ALTER TABLE ... SET POOLSIZE

▪ Define synchronization setting for tables. For more information, see ALTER TABLE ... SET HISTORY COLUMNS and ALTER TABLE ... SET SYNCHISTORY.

▪ Disable or enable compression on the table. For more information, see ALTER TABLE ... SET COMPRESSED or NONCOMPRESSED.

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 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 tab1 ADD COLUMN newcol INTEGER DEFAULT 1 NOT NULL;

Go up to

SQL: Statements