Modifies a table definition.
Syntax 1 - Alter Owner
ALTER TABLE <table_name> ALTER OWNER TO <new_owner> [ { PRESERVE | DROP } PERMISSIONS ] [ { PRESERVE | DROP } FOREIGN KEYS ]
Syntax 2
ALTER TABLE [ <owner>.]<table-name> |{ ENABLE | DISABLE } RLV STORE { alter-clause, ... } alter-clause - (back to Syntax 2) ADD create-clause | ALTER <column-name> column-alteration | ALTER [ CONSTRAINT <constraint-name> ] CHECK ( <condition> ) | DROP drop-object | RENAME rename-object | move-clause | SPLIT PARTITION <range-partition-name> INTO ( range-partition-decl, range-partition-decl ) | MERGE PARTITION <partition-name-1> INTO <partition-name-2> | UNPARTITION | PARTITION BY range-partitioning-scheme | hash-partitioning-scheme | composite-partitioning-scheme create-clause - (back to alter-clause) <column-name> column-definition [ column-constraint ] | table-constraint | [ PARTITION BY ] <range-partitioning-scheme> column definition - (back to create-clause) < column-name> <data-type> [ NOT NULL | NULL ] [ IN <dbspace-name> ] [ DEFAULT default-value | IDENTITY ] column-constraint - (back to create-clause) [ CONSTRAINT <constraint-name> ] { UNIQUE | PRIMARY KEY | REFERENCES <table-name> [ (<column-name> ) ] [ actions ] | CHECK ( <condition> ) | IQ UNIQUE ( <integer> ) } table-constraint - (back to create-clause) [ CONSTRAINT <constraint-name> ] { UNIQUE ( <column-name> [ , … ] ) | PRIMARY KEY ( <column-name> [ , … ] ) | foreign-key-constraint | CHECK ( <condition> ) } foreign-key-constraint - (back to table-constraint) FOREIGN KEY [ <role-name> ] [ ( <column-name> [ , … ] ) ] ... REFERENCES <table-name> [ ( <column-name> [ , … ] ) ] ... [ actions ] actions - (back to foreign-key-constraint) [ ON { UPDATE | DELETE } { RESTRICT } ] column-alteration - (back to alter-clause) { <column-data-type> | alterable-column-attribute } [ alterable-column-attribute … ] | ADD [ <constraint-name> ] CHECK ( <condition> ) | DROP { DEFAULT | CHECK | CONSTRAINT <constraint-name> } alterable-column-attribute - (back to column-alteration) [ NOT ] NULL | DEFAULT default-value | [ CONSTRAINT <constraint-name> ] CHECK { NULL |( <condition> ) } default-value - (back to alterable-column-attribute) CURRENT { DATABASE |DATE |REMOTE USER |TIME |TIMESTAMP | USER |PUBLISHER ) | <string> | <global variable> | [ - ] <number> | ( <constant-expression> ) | <built-in-function> ( <constant-expression> ) | AUTOINCREMENT | NULL | TIMESTAMP | LAST USER | USER drop-object - (back to alter-clause) { <column-name> | CHECK <constraint-name> | CONSTRAINT | UNIQUE ( <index-columns-list> ) | PRIMARY KEY | FOREIGN KEY <fkey-name> | [ PARTITION ] <range-partition-name> } rename-object - (back to alter-clause) <new-table-name> | <column-name> TO <new-column-name> | CONSTRAINT <constraint-name> TO <new-constraint-name> | [ PARTITION ] <range-partition-name> TO <new-range-partition-name> move-clause - (back to alter-clause) { <ALTER column-name> MOVE { PARTITION ( <range-partition-name> TO <new-dbspace-name>) | TO <new-dbspace-name> } } | MOVE PARTITION <range-partition-name> TO <new-dbspace-name> | MOVE TO <new-dbspace-name> | MOVE TABLE METADATA TO <new-dbspace-name> }
range-partitioning-scheme - (back to alter-clause) RANGE( partition-key ) ( range-partition-decl [,range-partition-decl ...] ) partition-key - (back to range-partitioning-scheme) <column-name> range-partition-decl - (back to alter-clause) or (back to range-partitioning-scheme) <range-partition-name> VALUES <= ( {<constant> | MAX } ) [ IN <dbspace-name> ] hash-partitioning-scheme - (back to alter-clause) or (back to composite-partitioning-scheme) HASH ( partition-key, … ] ) composite-partitioning-scheme - (back to alter-clause) hash-partitioning-scheme SUBPARTITION range-partitioning-scheme
add a new column to the table.
The table must be empty to specify NOT NULL. The table might contain data when you add an IDENTITY or DEFAULT AUTOINCREMENT column. If the column has a default IDENTITY value, all rows of the new column are populated with sequential values. You can also add FOREIGN constraint as a column constraint for a single column key. The value of the IDENTITY/DEFAULT AUTOINCREMENT column uniquely identifies every row in a table.
IQ UNIQUE constraint – Defines the expected cardinality of a column and determines whether the column loads as Flat FP or NBit FP. An IQ UNIQUE(<n>) value explicitly set to 0 loads the column as Flat FP. Columns without an IQ UNIQUE constraint implicitly load as NBit up to the limits defined by the FP_NBIT_AUTOSIZE_LIMIT, FP_NBIT_LOOKUP_MB, and FP_NBIT_ROLLOVER_MAX_MB options.
Consider memory usage when specifying high IQ UNIQUE values. If machine resources are limited, avoid loads with FP_NBIT_ENFORCE_LIMITS='OFF' (default).
Prior to SAP IQ 16.0, an IQ UNIQUE <n> value > 16777216 would rollover to Flat FP. In 16.0, larger IQ UNIQUE values are supported for tokenization, but may require significant memory resource requirements depending on cardinality and column width.
You can also add a foreign key constraint as a table constraint for a single-column or multicolumn key. If PRIMARY KEY is specified, the table must not already have a primary key created by the CREATE TABLE statement or another ALTER TABLE statement. See CREATE TABLE Statement for a full explanation of table constraints.
You cannot MODIFY a table or column constraint. To change a constraint, DELETE the old constraint and ADD the new constraint.
CREATE TABLE foo (c1 INT, c2 INT) PARTITION BY RANGE (c1) (P1 VALUES <= (100) IN dbsp1, P2 VALUES <= (200) IN dbsp2, P3 VALUES <= (MAX) IN dbsp3 ) IN dbsp4); LOAD TABLE …. ALTER TABLE DROP PARTITION P1;
MOVE TO – Move all table objects including columns, indexes, unique constraints, primary key, foreign keys, and metadata resided in the same dbspace as the table is mapped to the new dbspace. The ALTER Column MOVE TO clause cannot be requested on a partitioned table.
ALTER TABLE t2 alter c1_bit MOVE TO iq_main;
MOVE TABLE METADATA – Move the metadata of the table to a new dbspace. For a partitioned table, MOVE TABLE METADATA also moves metadata that is shared among partitions.
range-partition-decl: <partition-name> VALUES <= ( {<constant-expr> | MAX } [ , { <constant-expr> | MAX }]... ) [ IN <dbspace-name> ]
There is no lower bound (MIN value) for the first partition. Rows of NULL cells in the first column of the partition key will go to the first partition. For the last partition, you can either specify an inclusive upper bound or MAX. If the upper bound value for the last partition is not MAX, loading or inserting any row with partition key value larger than the upper bound value of the last partition generates an error.
In addition, partition bound values must be compatible with the corresponding partition-key column data type. For example, VARCHAR is compatible with CHAR.
CREATE TABLE Employees(emp_name VARCHAR(20)) PARTITION BY RANGE(emp_name) (p1 VALUES <=(CAST (1 AS VARCHAR(20))), p2 VALUES <= (CAST (10 AS VARCHAR(20)))
CREATE TABLE emp_id (id INT) PARTITION BY RANGE(id) (p1 VALUES <= (10.5), p2 VALUES <= (100.5))
CREATE TABLE id_emp (id FLOAT) PARTITION BY RANGE(id) (p1 VALUES <= (10), p2 VALUES <= (100))
CREATE TABLE newemp (name BINARY) PARTITION BY RANGE(name) (p1 VALUES <= ("Maarten"), p2 VALUES <= ("Zymmerman")
hash-partitioning-scheme:
HASH ( partition-key [ , partition-key, … ] )
hash-range-partitioning-scheme: PARTITION BY HASH ( partition-key [ , partition-key, … ] ) [ SUBPARTITION BY RANGE ( range-partition-decl [ , range-partition-decl ... ] ) ]
Range-partitions and composite partitioning schemes, like hash-range partitions, require the separately licensed VLDB Management option.
ALTER TABLE Employees ADD office CHAR(20)
ALTER TABLE Employees DROP office
Adds a column to the Customers table assigning each customer a sales contact:
ALTER TABLE Customers ADD SalesContact INTEGER REFERENCES Employees (EmployeeID)
ALTER TABLE Customers ADD CustomerNum INTEGER DEFAULT 88
CREATE TABLE foo ( c1 INT IN Dsp1, c2 VARCHAR(20), c3 CLOB IN Dsp2, c4 DATE, c5 BIGINT, PRIMARY KEY (c5) IN Dsp4) IN Dsp3); CREATE DATE INDEX c4_date ON foo(c4) IN Dsp5; ALTER TABLE foo MOVE TO Dsp6;
ALTER TABLE foo ALTER c1 MOVE TO Dsp7
Create a partitioned table:
CREATE TABLE bar ( c1 INT, c2 DATE, c3 VARCHAR(10)) PARTITION BY RANGE(c2) (p1 VALUES <= ('2005-12-31') IN dbsp1, p2 VALUES <= ('2006-12-31') IN dbsp2, P3 VALUES <= ('2007-12-31') IN dbsp3, P4 VALUES <= ('2008-12-31') IN dbsp4); INSERT INTO bar VALUES(3, '2007-01-01', 'banana nut'); INSERT INTO BAR VALUES(4, '2007-09-09', 'grape jam'); INSERT INTO BAR VALUES(5, '2008-05-05', 'apple cake');
Move partition p2 to dbsp5:
ALTER TABLE bar MOVE PARTITION p2 TO DBSP5;
Split partition p4 into 2 partitions:
ALTER TABLE bar SPLIT PARTITION p4 INTO (P41 VALUES <= ('2008-06-30') IN dbsp4, P42 VALUES <= ('2008-12-31') IN dbsp4);
This SPLIT PARTITION reports an error, as it requires data movement. Not all existing rows are in the same partition after split.
ALTER TABLE bar SPLIT PARTITION p3 INTO (P31 VALUES <= ('2007-06-30') IN dbsp3, P32 VALUES <= ('2007-12-31') IN dbsp3);
This error is reported:
No data move is allowed, cannot split partition p3.
This SPLIT PARTITION reports an error, because it changes the partition boundary value:
ALTER TABLE bar SPLIT PARTITION p2 INTO (p21 VALUES <= ('2006-06-30') IN dbsp2, P22 VALUES <= ('2006-12-01') IN dbsp2);
This error is reported:
Boundary value for the partition p2 cannot be changed.
Merge partition p3 into p2. An error is reported as a merge from a higher boundary value partition into a lower boundary value partition is not allowed.
ALTER TABLE bar MERGE PARTITION p3 into p2;
This error is reported:
Partition 'p2' is not adjacent to or before partition 'p3'.
Merge partition p2 into p3:
ALTER TABLE bar MERGE PARTITION p2 INTO P3;
Rename partition p1 to p1_new:
ALTER TABLE bar RENAME PARTITION p1 TO p1_new;
Unpartition table bar:
ALTER TABLE bar UNPARTITION;
Partition table bar. This command reports an error, because all rows must be in the first partition.
ALTER TABLE bar PARTITION BY RANGE(c2) (p1 VALUES <= ('2005-12-31') IN dbsp1, P2 VALUES <= ('2006-12-31') IN DBSP2, P3 VALUES <= ('2007-12-31') IN dbsp3, P4 VALUES <= ('2008-12-31') IN dbsp4);
This error is reported:
All rows must be in the first partition.
Partition table bar:
ALTER TABLE bar PARTITION BY RANGE(c2) (p1 VALUES <= ('2008-12-31') IN dbsp1, P2 VALUES <= ('2009-12-31') IN dbsp2, P3 VALUES <= ('2010-12-31') IN dbsp3, P4 VALUES <= ('2011-12-31') IN dbsp4);
ALTER TABLE tab1 DISABLE RLV STORE
The ALTER TABLE statement changes table attributes (column definitions and constraints) in a table that was previously created. The syntax allows a list of alter clauses; however, only one table constraint or column constraint can be added, modified, or deleted in each ALTER TABLE statement. ALTER TABLE is prevented whenever the statement affects a table that is currently being used by another connection. ALTER TABLE can be time consuming, and the server does not process requests referencing the same table while the statement is being processed.
You cannot alter local temporary tables, but you can alter global temporary tables when they are in use by only one connection.
SAP IQ enforces REFERENCES and CHECK constraints. Table and/or column check constraints added in an ALTER TABLE statement are evaluated, only if they are defined on one of the new columns added, as part of that alter table operation. For details about CHECK constraints, see CREATE TABLE Statement.
If SELECT <*> is used in a view definition and you alter a table referenced by the SELECT <*> , then you must run ALTER VIEW <viewname> RECOMPILE to ensure that the view definition is correct and to prevent unexpected results when querying the view.
Automatic commit. The ALTER and DROP options close all cursors for the current connection. The Interactive SQL data window is also cleared.
A checkpoint is carried out at the beginning of the ALTER TABLE operation.
Once you alter a column or table, any stored procedures, views or other items that refer to the altered column no longer work.