ALTER TABLE statement

Description

Modifies a table definition.

Syntax

ALTER TABLEowner.]table-namealter-clause, ... }

Parameters

alter-clause:

 ADD create-clause    | ALTER column-name  column-alteration  | ALTER  [ CONSTRAINT constraint-name ] CHECK ( condition )   | DROP drop-object  |  RENAME rename-object  | move-clause  | SPLIT PARTITION partition-name INTO  ( partition-decl-1partition-decl-2 )   | MERGE PARTITION partition-name-1 INTO partition-name-2  | UNPARTITION  | PARTITION BY RANGE ( partition-key ) range-partition-decl

create-clause:

 column-name column-definition [ column-constraint ]   | table-constraint  | PARTITION BY partitioning-schema

column-alteration:

column-data-type | alterable-column-attribute } [ alterable-column-attribute… ]  | ADD [ constraint-name ] CHECK ( condition )  | DROP { DEFAULT | CHECK | CONSTRAINT constraint-name }

alterable-column-attribute:

[NOT] NULL  | DEFAULT default-value  | [ CONSTRAINT constraint-name ] CHECKNULL | (condition ) }

column-constraint:

CONSTRAINT constraint-name ] { UNIQUE | PRIMARY KEY | REFERENCES table-name [ ( column-name ) ] [ actions ] | CHECKcondition ) | IQ UNIQUEinteger ) }

drop-object:

 { column-name  | CHECK|  | CONSTRAINT constraint-name  | UNIQUE ( index-columns-list )   | PRIMARY KEY  | FOREIGN KEY fkey-name  | PARTITION range-partition-name }

move-clause:

ALTER column-name MOVE    { PARTITION ( partition-name TO new-dbspace-name )     | TO new-dbspace-name } }  | MOVE PARTITION partition-name TO new-dbspace-name   | MOVE TO new-dbspace-name  | MOVE METADATA TO new-dbspace-name

rename-object:

new-table-name  | column-name TO new-column-name   | CONSTRAINT constraint-name TO new-constraint-name  | PARTITION partition-name TO new-partition-name

column-definition:

column-name data-typeNOT NULL ] [ DEFAULT default-value | IDENTITY ]

default-value:

special-value | string | global variable | [ - ] number | ( constant-expression ) | built-in-function constant-expression ) | AUTOINCREMENT | NULL | TIMESTAMP | LAST USER | USER

special-value:

CURRENTDATABASE | DATE | REMOTE USER | TIME | TIMESTAMP | USER | PUBLISHER }

table-constraint:

CONSTRAINT constraint-name ] { UNIQUEcolumn-name [ , … ] ) | PRIMARY KEYcolumn-name [ , … ] ) | foreign-key-constraint | CHECKcondition ) }

foreign-key-constraint:

FOREIGN KEYrole-name ] [ ( column-name [ , … ] ) ] ... REFERENCES table-name [ ( column-name [ , … ] ) ] ... [ actions ] [

rename-object:

new-table-name | column-name TO new-column-name   | CONSTRAINT constraint-name TO new-constraint-name | PARTITION partition-name TO new-partition-name

range-partitioning-scheme:

RANGEpartition-key ) ( range-partition-decl [,range-partition-decl ...] )

partition-key:

column-name

range-partition-decl:

partition-name VALUES <= ( {constant |  MAX } ) [ IN dbspace-name ]

actions:

ON { UPDATE | DELETE } action ]

action:

RESTRICT }

Examples

Example 1

Adds a new column to the Employees table showing which office they work in:

ALTER TABLE Employees
ADD office CHAR(20)

Example 2

Drops the office column from the Employees table:

ALTER TABLE Employees
DROP office

Example 3

Adds a column to the Customers table assigning each customer a sales contact:

ALTER TABLE Customers
ADD SalesContact INTEGER
REFERENCES Employees (EmployeeID)

Example 4

Adds a new column CustomerNum to the Customers table and assigns a default value of 88:

ALTER TABLE Customers
ADD CustomerNum INTEGER DEFAULT 88

Example 5

Only FP indexes for c2, c4 and c5, are moved from dbspace Dsp3 to Dsp6. FP index for c1 remains in Dsp1. FP index for c3 remains in Dsp2. The primary key for c5 remains in Dsp4. Date index c4_date remains in Dsp5.

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;

Example 6

Moves only FP index c1 from dbspace Dsp1 to Dsp7.

ALTER TABLE foo ALTER c1 MOVE TO Dsp7

Example 7

This example illustrates the use of many ALTER TABLE clauses to move, split, rename, and merge partitions.

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 will be 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);

Usage

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.

NoteYou cannot alter local temporary tables, but you can alter global temporary tables when they are in use by only one connection.

Sybase IQ enforces REFERENCES and CHECK constraints. Table and/or column check constraints added in an ALTER TABLE statement are not evaluated 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.

ADD column-definition [ column-constraint ] 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 a foreign key 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. The IDENTITY/DEFAULT AUTOINCREMENT column stores sequential numbers that are automatically generated during inserts and updates. DEFAULT AUTOINCREMENT columns are also known as IDENTITY columns. When using IDENTITY/DEFAULT AUTOINCREMENT, the column must be one of the integer data types, or an exact numeric type, with scale 0. See CREATE TABLE statement for more about column constraints and IDENTITY/DEFAULT AUTOINCREMENT columns.

NoteYou cannot add foreign key constraints to an unenforced primary key created with Sybase IQ version 12.4.3 or earlier.

ALTER column-name column-alteration Change the definition of a column. The permitted modifications are as follows:

DROP partition clause The DROP partition clause drops the specified partition. The rows are deleted and the partition definition is dropped. You cannot drop the last partition because dropping the last partition would transform a partitioned table to a non-partitioned table. (To merge a partitioned table, use UNPARTITION clause instead.) For example:

	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;

ADD table-constraint Add a constraint to the table. You can also add a foreign key constraint as a table constraint for a single-column or multicolumn key. See CREATE TABLE statement for a full explanation of table constraints.

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.

NoteYou cannot MODIFY a table or column constraint. To change a constraint, DELETE the old constraint and ADD the new constraint.

DROP column-name Drop the column from the table. If the column is contained in any multicolumn index, uniqueness constraint, foreign key, or primary key, then the index, constraint, or key must be deleted before the column can be deleted. This does not delete CHECK constraints that refer to the column. An IDENTITY/DEFAULT AUTOINCREMENT column can only be deleted if IDENTITY_INSERT is turned off and the table is not a local temporary table.

DROP CHECK Drop all check constraints for the table. This includes both table check constraints and column check constraints.

DROP CONSTRAINT constraint-name Drop the named constraint for the table or specified column.

DROP UNIQUE (column-name,…) Drop the unique constraints on the specified column(s). Any foreign keys referencing the unique constraint (rather than the primary key) are also deleted. Reports an error if there are associated foreign-key constraints. Use ALTER TABLE to delete all foreign keys that reference the primary key before you delete the primary key constraint.

DROP PRIMARY KEY Drop the primary key. All foreign keys referencing the primary key for this table are also deleted. Reports an error if there are associated foreign key constraints. If the primary key is unenforced, DELETE returns an error if associated unenforced foreign key constraints exist.

DROP FOREIGN KEY role-name Drop the foreign key constraint for this table with the given role name. Retains the implicitly created nonunique HG index for the foreign key constraint. Users can explicitly remove the HG index with the DROP INDEX statement.

DROP PARTITION The DROP PARTITION request deletes rows in partition P1 and drops the partition definition of P1. If a new row with value 99 for column c1 is inserted, it will be placed under partition p2 in dbspace dbsp2.

RENAME new-table-name Change the name of the table to the new-table-name. Any applications using the old table name must be modified. Also, any foreign keys that were automatically assigned the same name as the old table name do not change names.

RENAME column-name TO new-column-name Change the name of the column to the new-column-name. Any applications using the old column name must be modified.

RENAME constraint-name TO new-constraint-name Change the name of the constraint to the new-constraint-name. Any applications using the old constraint name must be modified.

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.

ALTER Column MOVE TO The ALTER Column MOVE TO clause moves the specified column to the new dbspace for a non-partitioned table. The ALTER Column MOVE TO clause cannot be requested on a partitioned table. The ALTER Column MOVE PARTITION clause moves the column of the specified partition to the specified dbspace.

MOVE PARTITION The MOVE PARTITION clause moves the specified partition to the new dbspace.

MOVE TO The MOVE TO clause moves 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.

Each table object can reside in only one dbspace. Any type of ALTER MOVE blocks any modification to the table for the entire duration of the move.

MOVE TABLE METADATA The MOVE TABLE METADATA clause moves the metadata of the table, such as the EBM, DeleteBM, and InsertBM of the table, to a new dbspace. For a partitioned table, the MOVE TABLE METADATA clause also moves metadata that is shared among partitions.

You must have DBA or SPACE ADMIN authority, or have CREATE privilege on the new dbspace and be the table owner or have alter permission on the table.

SPLIT PARTITION The SPLIT PARTITION clause splits the specified partition into two partitions. In Sybase IQ 15.2, a partition can be split only if no data must be moved. All existing rows of the partition to be split must remain in a single partition after the split. The boundary value for partition-decl-1 must be less than the boundary value of partition-name and the boundary value for partition-decl-2 must be equal to the boundary value of partition-name. You can specify different names for the two new partitions. The old partition-name can only be used for the second partition, if a new name is not specified.

MERGE PARTITION The MERGE PARTITION clause merges partition-name-1 into partition-name-2. In Sybase IQ 15.2, two partitions can be merged if they are adjacent partitions and the data resides on the same dbspace. You can only merge a partition with a lower partition value into the adjacent partition with a higher partition value. Note that the server does not check CREATE permission on the dbspace into which the partition is merged. For an example of how to create adjacent partitions, see Example 3 in CREATE TABLE statement.

UNPARTITION The UNPARTITION keyword removes partitions from a partitioned table. Each column is placed in a single dbspace. Note that the server does not check CREATE permission on the dbspace to which data of all partitions is moved. ALTER TABLE UNPARTITION blocks all database activities.

PARTITION BY The PARTITION BY clause partitions a non-partitioned table. In Sybase IQ 15.2, a non-partitioned table can be partitioned, if all existing rows belong to the first partition. You can specify a different dbspace for the first partition than the dbspace of the column or table. But existing rows are not moved. Instead, the proper dbspace for the column/partition is kept in SYS.ISYSIQPARTITIONCOLUMN for existing columns. Only the default or max identity column(s) that are added later for the first partition are stored in the specified dbspace for the first partition.

RENAME PARTITION The RENAME PARTITION clause renames an existing partition name to a new partition name.


Side effects

Standards

Permissions

For MOVE syntax, one of the following must be true:

For syntax other than MOVE, one of the following must be true:

Requires exclusive access to the table.

See also

CREATE TABLE statement

DROP statement

“IDENTITY_INSERT option”

Chapter 3, “SQL Data Types” in Reference: Building Blocks, Tables, and Procedures