ALTER TABLE Statement

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

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

Parameters

(back to top)

• { ENABLE | DISABLE } RLV STORE – registers this table with the RLV store for real-time in-memory updates. Not supported for IQ temporary tables or in multiplex environments. This value overrides the value of the database option BASE_TABLES_IN_RLV.

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

  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.

  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.

  Using IQ UNIQUE with an n value less than the FP_NBIT_AUTOSIZE_LIMIT is not necessary. Auto-size functionality automatically sizes all low or medium cardinality columns as NBit. Use IQ UNIQUE in cases where you want to load the column as Flat FP or when you want to load a column as NBit when the number of distinct values exceeds the FP_NBIT_AUTOSIZE_LIMIT.

  Note:

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

  • BIT, BLOB,and CLOB data types do not support NBit dictionary compression. If FP_NBIT_IQ15_COMPATIBILITY=’OFF’, a non-zero IQ UNIQUE column specification in a CREATE TABLE or ALTER TABLE statement that includes these data types returns an error.

• ALTER column-name column-alteration – change the column definition:
  • SET DEFAULT default-value – Change the default value of an existing column in a table. You can also use the MODIFY clause for this task, but ALTER is ISO/ANSI SQL compliant, and MODIFY is not. Modifying a default value does not change any existing values in the table.
  • DROP DEFAULT – Remove the default value of an existing column in a table. You can also use the MODIFY clause for this task, but ALTER is ISO/ANSI SQL compliant, and MODIFY is not. Dropping a default does not change any existing values in the table.
  • ADD – Add a named constraint or a CHECK condition to the column. The new constraint or condition applies only to operations on the table after its definition. The existing values in the table are not validated to confirm that they satisfy the new constraint or condition.
  • CONSTRAINT column-constraint-name – The optional column constraint name lets you modify or drop individual constraints at a later time, rather than having to modify the entire column constraint.
  • [ CONSTRAINT constraint-name ] CHECK ( condition ) – Use this clause to add a CHECK constraint on the column.
  • SET COMPUTE (expression) – Change the expression associated with a computed column. The values in the column are recalculated when the statement is executed, and the statement fails if the new expression is invalid.
  • DROP COMPUTE – Change a column from being a computed column to being a non-computed column. This statement does not change any existing values in the table.

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

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

• DROP drop-object – drops a table object:
  • 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 non-unique HG index for the foreign key constraint. Users can explicitly remove the HG index with the DROP INDEX statement.
  • DROP [ PARTITION ] – Drop the specified partition. The rows in partition P1 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 an 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;

• RENAME rename-object – renames an object in the table:
  • 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 new-column-name. Any applications using the old column name must be modified.
  • RENAME [ PARTITION ] – Rename an existing partition.
  • RENAME constraint-name TO new-constraint-name – Change the name of the constraint to new-constraint-name. Any applications using the old constraint name must be modified.

• MOVE clause – moves a table object. A table object can only reside in one dbspace. Any type of ALTER MOVE blocks any modification to the table for the entire duration of the move.
  Note: You cannot move objects to a cache dbspace.

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

    A BIT data type column cannot be explicitly placed in a dbspace. The following is not supported for BIT data types:

    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.

  • MOVE PARTITION – Move the specified partition to the new dbspace.

• PARTITION BY – divides large tables into smaller, more manageable storage objects. Partitions share the same logical attributes of the parent table, but can be placed in separate dbspaces and managed individually. SAP Sybase IQ supports several table partitioning schemes:
  • hash-partitions
  • range-partitions
  • composite-partitions
  A partition-key is the column or columns that contain the table partitioning keys. Partition keys can contain NULL and DEFAULT values, but cannot contain:

  • LOB (BLOB or CLOB) columns
  • BINARY, or VARBINARY columns
  • CHAR or VARCHAR columns whose length is over 255 bytes
  • BIT columns
  • FLOAT/DOUBLE/REAL columns

• PARTITION BY RANGE – partitions rows by a range of values in the partitioning column. Range partitioning is restricted to a single partition key column and a maximum of 1024 partitions. In a range-partitioning-scheme, the partition-key is the column that contains the table partitioning keys:

  range-partition-decl:
    partition-name VALUES <= ( {constant-expr |  MAX } [ , { constant-expr |  MAX }]... ) 
      [ IN dbspace-name ]

  The partition-name is the name of a new partition on which table rows are stored. Partition names must be unique within the set of partitions on a table. The partition-name is required.
  • VALUE – specifies the inclusive upper bound for each partition (in ascending order). The user must specify the partitioning criteria for each range partition to guarantee that each row is distributed to only one partition. NULLs are allowed for the partition column and rows with NULL as partition key value belong to the first table partition. However, NULL cannot be the bound value.

    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.

  • Max – denotes the infinite upper bound and can only be specified for the last partition.
  • IN – specifies the dbspace in the partition-decl on which rows of the partition should reside.
  These restrictions affect partitions keys and bound values for range partitioned tables:

  • You can only range partition a non-partitioned table if all existing rows belong to the first partition.
  • Partition bounds must be constants, not constant expressions.
  • Partition bounds must be in ascending order according to the order in which the partitions were created. That is, the upper bound for the second partition must be higher than for the first partition, and so on.

    In addition, partition bound values must be compatible with the corresponding partition-key column data type. For example, VARCHAR is compatible with CHAR.

  • If a bound value has a different data type than that of its corresponding partition key column, SAP Sybase IQ converts the bound value to the data type of the partition key column, with these exceptions:
  • Explicit conversions are not allowed. This example attempts an explicit conversion from INT to VARCHAR and generates an error:

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

  • Implicit conversions that result in data loss are not allowed. In this example, the partition bounds are not compatible with the partition key type. Rounding assumptions may lead to data loss and an error is generated:

    CREATE TABLE emp_id (id INT) PARTITION BY RANGE(id) (p1 VALUES <= (10.5), p2 VALUES <= (100.5))

  • In this example, the partition bounds and the partition key data type are compatible. The bound values are directly converted to float values. No rounding is required, and conversion is supported:

    CREATE TABLE id_emp (id FLOAT)
    PARTITION BY RANGE(id) (p1 VALUES <= (10), 
    p2 VALUES <= (100))

  • Conversions from non-binary data types to binary data types are not allowed. For example, this conversion is not allowed and returns an error:

    CREATE TABLE newemp (name BINARY)
    PARTITION BY RANGE(name) 
    (p1 VALUES <= ("Maarten"), 
    p2 VALUES <= ("Zymmerman")

  • NULL cannot be used as a boundary in a range-partitioned table.
  • The row will be in the first partition if the cell value of the 1st column of the partition key evaluated to be NULL. SAP Sybase IQ supports only single column partition keys, so any NULL in the partition key distributes the row to the first partition.

• PARTITION BY HASH – maps data to partitions based on partition-key values processed by an internal hashing function. Hash partition keys are restricted to a maximum of eight columns with a combined declared column width of 5300 bytes or less. For hash partitions, the table creator determines only the partition key columns; the number and location of the partitions are determined internally.
  In a hash-partitioning declaration, the partition-key is a column or group of columns, whose composite value determines the partition where each row of data is stored:

  hash-partitioning-scheme: 
    HASH  ( partition-key [ , partition-key, … ] )

  • Restrictions –
    • You can only hash partition a base table. Attempting to partitioning a global temporary table or a local temporary table raises an error.
    • You can only hash partition a non-partitioned table that is empty.
    • You cannot add, drop, merge, or split a hash partition.
    • You cannot add or drop a column from a hash partition key.
• PARTITION BY HASH RANGE – subpartitions a hash-partitioned table by range. In a hash-range-partitioning-scheme declaration, a SUBPARTITION BY RANGE clause adds a new range subpartition to an existing hash-range partitioned table:

  hash-range-partitioning-scheme:                
  PARTITION BY HASH  ( partition-key [ , partition-key, … ] )
      [ SUBPARTITION BY RANGE  ( range-partition-decl [ , range-partition-decl ... ] ) ]

  The hash partition specifies how the data is logically distributed and colocated; the range subpartition specifies how the data is physically placed. The new range subpartition is logically partitioned by hash with the same hash partition keys as the existing hash-range partitioned table. The range subpartition key is restricted to one column.
  • Restrictions –
    • You can only hash partition a base table. Attempting to partitioning a global temporary table or a local temporary table raises an error.
    • You can only subpartition a hash-partitioned table by range if the the table is empty.
    • You cannot add, drop, merge, or split a hash partition.
    • You cannot add or drop a column from a hash partition key.
    Note: Range-partitions and composite partitioning schemes, like hash-range partitions, require the separately licensed VLDB Management option.

• 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 privilege 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 – rename an existing PARTITION.

• UNPARTITION – remove partitions from a partitioned table. Each column is placed in a single dbspace. Note that the server does not check CREATE privilege 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 privileges 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 privileges (such as those given to the owner of a table). To avoid this failure, explicitly grant SELECT privileges 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 privileges (such as those given to the owner of a table). To avoid this failure, explicitly GRANT SELECT privileges 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 privilege of the sequence generator relies on implicitly-granted privileges (such as those given to the owner of a sequence). To avoid this failure, explicitly grant USAGE privilege on the sequence generator to the new owner of the table.
  • Enabled materialized views that depend on the original table exist.

Examples

(back to top)

• 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 – moves FP indexes for c2, c4, and c5, 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 – uses 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 – changes 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

(back to top)

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.

Standards

(back to top)

Permissions

(back to top)

Syntax 1

Syntax 2

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