ALTER TABLE Statement

Modifies a table definition.

Quick Links:

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)

Examples

(back to top)

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.

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

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.

Side effects:

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

Standards

(back to top)

  • SQL–Vendor extension to ISO/ANSI SQL grammar.
  • SAP Sybase Database product–Some clauses are supported by SAP Adaptive Server® Enterprise.

Permissions

(back to top)

Syntax 1

Requires one of:
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
  • ALTER privilege on the table
  • You own the table

Syntax 2

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

Clause Privilege Required
Add Requires one of:
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
  • ALTER privilege on the underlying table
  • You own the underlying table

UNIQUE, PRIMARY KEY, FOREIGN KEY, or IQ UNIQUE column constraint – Requires above along with REFERENCES privilege on the underlying table.

FOREIGN KEY table constraint requires above along with one of:
  • CREATE ANY INDEX system privilege
  • CREATE ANY OBJECT system privilege
  • REFERENCES privilege on the base table
PARTITION BY RANGE requires above along with one of:
  • CREATE ANY OBJECT system privilege
  • CREATE privilege on the dbspaces where the partitions are being created
Alter Requires one of:
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
  • ALTER privilege on the table
  • You own the table.

To alter a primary key or unique constraint, also requires REFERENCES privilege on the table.
Drop Drop a column with no constraints – Requires one of:
  • ALTER ANY OBJECT system privilege
  • ALTER ANY TABLE system privilege
  • ALTER privilege on the underlying table
  • You own the underlying table

Drop a column or table with a constraint requires above along with REFERENCES privilege if using ALTER privilege.

Drop a partition on table owned by self – None required.

Drop a partition on table owned by other users – Requires one of:
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
  • ALTER privilege on the table
RENAME Requires one of:
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
  • ALTER privilege on the table
  • You own the table
Move Requires one of:
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
  • system privilege
  • ALTER privilege on the underlying table
  • You own the underlying table
Also requires one of the following:
  • CREATE ANY OBJECT system privilege
  • CREATE privilege on the dbspace to which the partition is being moved
Split Partition Partition on table owned by self – None required.
Partition on table owned by other users – Requires one of:
  • SELECT ANY TABLE system privilege
  • SELECT privilege on table
Also requires one of:
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
  • ALTER privilege on the table
Merge Partition, Unpartition Table owned by self – None required.
Table owned by other users – Requires one of:
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
  • ALTER privilege on the table
Partition By Requires one of:
  • CREATE ANY OBJECT system privilege
  • CREATE privilege on the dbspaces where the partitions are being created
Also requires one of:
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
  • ALTER privilege on the table
  • You own the table
or disable RLV store Requires one of:
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
Parent topic: SQL Statements
Related reference
CREATE TABLE Statement
DROP Statement
IDENTITY_INSERT Option
FP_NBIT_AUTOSIZE_LIMIT Option
FP_NBIT_ENFORCE_LIMITS Option
FP_NBIT_LOOKUP_MB Option
FP_NBIT_ROLLOVER_MAX_MB Option