ALTER TABLE Statement

Syntax

Syntax 1

ALTER TABLE [ owner.]table-name
|{ ENABLE | DISABLE } RLV STORE
{ alter-clause, ... }

Syntax 2

ALTER TABLE table_name ALTER OWNER TO new_owner 
   [ { PRESERVE | DROP } PERMISSIONS ] 
   [ { PRESERVE | DROP } FOREIGN KEYS ]

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 range-partition-name 
          INTO( range-partition-decl-1, range-partition-decl-2 
     | SPLIT SUBPARTITION range-subpartition-name 
          INTO ( subrange-partition-decl-1, subrange-partition-decl-2 ) 
     | MERGE PARTITION partition-name-1 INTO partition-name-2 
     | MERGE SUBPARTITION subrange-partition-name-1 INTO subrange-partition-name-2 
     | UNPARTITION
     | PARTITION BY 
        range-partitioning-scheme 
         | hash-partitioning-scheme 
         | composite-partitioning-scheme 

• create-clause –

  column-name column-definition [ column-constraint ]
     | table-constraint
     | [ PARTITION BY | SUBPARTITION BY ] range-partitioning-scheme

• column-definition –

   column-name data-type [ NOT NULL | NULL  ] 
      [ IN dbspace-name ] 
      [ DEFAULT default-value | IDENTITY ]

• column-constraint –

  [ CONSTRAINT constraint-name ] 
     { UNIQUE
       | PRIMARY KEY
       | REFERENCES table-name [ (column-name ) ] [ actions ] 
       | CHECK ( condition ) 
       | IQ UNIQUE ( integer ) 
     }

• table-constraint –

  [ CONSTRAINT constraint-name ] 
      { UNIQUE ( column-name [ , … ] )
        | PRIMARY KEY ( column-name [ , … ] ) 
        | foreign-key-constraint 
        | CHECK ( condition )
     }

• foreign-key-constraint –

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

• actions –

  [ ON  { UPDATE | DELETE }   { RESTRICT } ]

• 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 ] CHECK { NULL |( condition ) 
       }

• default-value –

   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 –

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

• rename-object –

  new-table-name  
      | column-name TO new-column-name   
      | CONSTRAINT constraint-name TO new-constraint-name  
      | [ PARTITION | SUBPARTITION ] range-partition-name TO new-range-partition-name 

• move-clause –

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

• range-partitioning-scheme –

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

• hash-partitioning-scheme –

  HASH ( partition-key [, partition-key,…] )

• composite-partitioning-schem: –

  hash-partitioning scheme SUBPARTITION range-partitioning-scheme 

• partition-key –

  column-name

• range-partition-decl –

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

Examples

• Example 1 – Add a new column to the Employees table showing which office they work in:

  ALTER TABLE Employees
  ADD office CHAR(20)

• Example 2 – Drop the office column from the Employees table:

  ALTER TABLE Employees
  DROP office

• Example 3 – Add a column to the Customers table assigning each customer a sales contact:

  ALTER TABLE Customers
  ADD SalesContact INTEGER
  REFERENCES Employees (EmployeeID)

• Example 4 – Add 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 – Move 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 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);

• Example 8 – Change a table tab1 so that it is no longer registered for in-memory real-time updates in the RLV store.

  ALTER TABLE tab1 DISABLE RLV STORE

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. 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.

SAP Sybase 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.

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. 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.

MOVE clause — Moves a table object.

PARTITION BY — Partitions a non-partitioned table. 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.

MERGE PARTITION — Merge partition-name-1 into partition-name-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 CREATE TABLE Statement examples.

RENAME [ PARTITION | SUBPARTITION ] — Rename an existing PARTITION or SUBPARTITION.

UNPARTITION — Remove 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.

ALTER OWNER – Change the owner of a table. The ALTER OWNER clause may not be used in conjunction with any other [alter-clause] clauses of the ALTER TABLE statement.

• [ PRESERVE | DROP ] PERMISSIONS – If you do not want the new owner to have the same privileges as the old owner, use the DROP permissions clause (default) to drop all explicitly-granted privileges that allow a user access to the table. Implicitly-granted privileges given to the owner of the table are given to the new owner and dropped from the old owner.

  [ PRESERVE | DROP ] FOREIGN KEYS If you want to prevent the new owner from accessing data in referenced tables, use the DROP FOREIGN KEYS clause (default) to drop all foreign keys within the table, as well as all foreign keys referring to the table. Use of the PRESERVE FOREIGN KEYS clause with the DROP PERMISSIONS clause fails unless all referencing tables are owned by the new owner.

• The ALTER TABLE ALTER OWNER statement fails if:
  • Another table with the same name as the original table exists and is owned by the new user.
  • The PRESERVE FOREIGN KEYS and PRESERVE PERMISSIONS clauses are both specified and there is a foreign key owned by a user other than the new table owner referencing the table that relies on implicitly-granted permissions (such as those given to the owner of a table). To avoid this failure, explicitly grant SELECT permissions to the referring table's original owner, or drop the foreign keys.
  • The PRESERVE FOREIGN KEYS clause is specified, but the PRESERVE PERMISSIONS clause is NOT, and there is a foreign key owned by a user other than the new table owner referencing the table. To avoid this failure, drop the foreign keys.
  • The PRESERVE FOREIGN KEYS clause is specified and the table contains a foreign key that relies on implicitly-granted permissions (such as those given to the owner of a table). To avoid this failure, explicitly GRANT SELECT permissions to the new owner on the referenced table, or drop the foreign keys.
  • The table contains a column with a default value that refers to a sequence, and the USAGE permission of the sequence generator relies on implicitly-granted permissions (such as those given to the owner of a sequence). To avoid this failure, explicitly grant USAGE permission on the sequence generator to the new owner of the table.
  • Enabled materialized views that depend on the original table exist.

Standards

Permissions

Syntax 1

The system privileges required for syntax 1 varies depending upon the clause used.

Syntax 2