ALTER TABLE

Function

ALTER TABLE modifies tables, including modifying table definitions, renaming tables, renaming specified columns in tables, renaming table constraints, setting table schemas, enabling or disabling row-level security policies, and adding or updating multiple columns.

Precautions

• The owner of a table, users granted with the ALTER permission on the table, or users granted with the ALTER ANY TABLE permission can run the ALTER TABLE statement. The system administrator has the permission to run the command by default. To modify the owner or schema of a table, you must be the table owner or system administrator and a member of the new owner role.
• The tablespace of a partitioned table cannot be modified, but the tablespace of the partition can be modified.
• The storage parameter ORIENTATION cannot be modified.
• Currently, SET SCHEMA can only set schemas to user schemas. It cannot set a schema to a system internal schema.
• Column-store tables support only the PARTIAL CLUSTER KEY, UNIQUE, and PRIMARY KEY table-level constraints, but do not support foreign key table-level constraints.
• In a column-store table, you can perform ADD COLUMN, ALTER TYPE, SET STATISTICS, DROP COLUMN operations, and change table name and space. The types of new and modified columns should be the Data Types supported by column-store. The USING option of ALTER TYPE only supports constant expression and expression involved in the column.
• The column constraints supported by column-store tables include NULL, NOT NULL, DEFAULT constant values, UNIQUE, and PRIMARY KEY. Only the DEFAULT value can be modified (by using SET DEFAULT and DROP DEFAULT). Currently, NULL and NOT NULL constraints cannot be modified.

Syntax

• Modify the definition of a table.

  ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
      action [, ... ];

  There are several clauses of action:

  column_clause
      | ADD table_constraint [ NOT VALID ]
      | ADD table_constraint_using_index
      | VALIDATE CONSTRAINT constraint_name
      | DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
      | CLUSTER ON index_name
      | SET WITHOUT CLUSTER
      | SET ( {storage_parameter = value} [, ... ] )
      | RESET ( storage_parameter [, ... ] )
      | OWNER TO new_owner
      | SET TABLESPACE new_tablespace
      | SET {COMPRESS|NOCOMPRESS}
      | TO { GROUP groupname | NODE ( nodename [, ... ] ) }
      | ADD NODE ( nodename [, ... ] )
      | DELETE NODE ( nodename [, ... ] )
      | DISABLE TRIGGER [ trigger_name | ALL | USER ]
      | ENABLE TRIGGER [ trigger_name | ALL | USER ]
      | ENABLE REPLICA TRIGGER trigger_name
      | ENABLE ALWAYS TRIGGER trigger_name
      | DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
      | DISABLE ROW LEVEL SECURITY
      | ENABLE ROW LEVEL SECURITY
      | FORCE ROW LEVEL SECURITY
      | NO FORCE ROW LEVEL SECURITY
      | ENCRYPTION KEY ROTATION
      | INHERIT parents
      | NO INHERIT parents
      | OF type_name
      | NOT OF
      | REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

  

  • ADD table_constraint [ NOT VALID ]

    Adds a table constraint.

  • ADD table_constraint_using_index

    Adds a primary key constraint or unique constraint to a table based on the existing unique index.

  • VALIDATE CONSTRAINT constraint_name

    Validates a check-class constraint created with the NOT VALID option, and scans the entire table to ensure that all rows meet the constraint. Nothing happens if the constraint is already marked valid.

  • DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

    Deletes a table constraint.

  • CLUSTER ON index_name

    Selects the default index for future CLUSTER operations. Actually, the table is not re-clustered.

  • SET WITHOUT CLUSTER

    Deletes the most recently used CLUSTER index from the table. This affects future CLUSTER operations that do not specify an index.

  • SET ( {storage_parameter = value} [, ... ] )

    Changes one or more storage parameters for the table.

  • RESET ( storage_parameter [, ... ] )

    Resets one or more storage parameters to their defaults. As with SET, a table rewrite might be needed to update the table entirely.

  • OWNER TO new_owner

    Changes the owner of a table, sequence, or view to the specified user.

  • SET TABLESPACE new_tablespace

    Changes the table's tablespace to the specified tablespace and moves the data files associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE option in ALTER INDEX.

  • SET {COMPRESS|NOCOMPRESS}

    Sets the compression feature of a table. The table compression feature affects only the storage mode of data inserted in a batch subsequently and does not affect storage of existing data. Setting the table compression feature will result in the fact that there are both compressed and uncompressed data in the table. Row-store tables do not support compression.

  • TO { GROUP groupname | NODE ( nodename [, ... ] ) }

    The syntax is only available in extended mode (when GUC parameter support_extended_features is on). Exercise caution when enabling the mode. It is mainly used for tools like internal dilatation tools. Common users should not use the mode.

  • ADD NODE ( nodename [, ... ] )

    It is only available for internal scale-out tools. Common users should not use the syntax.

  • DELETE NODE ( nodename [, ... ] )

    It is only available for internal scale-in tools. Common users should not use the syntax.

  • DISABLE TRIGGER [ trigger_name | ALL | USER ]

    Disables a single trigger specified by trigger_name, disables all triggers, or disables only user triggers (excluding internally generated constraint triggers, for example, deferrable unique constraint triggers and exclusion constraints triggers).

    Exercise caution when using this function because data integrity cannot be ensured as expected if the triggers are not executed.

  • | ENABLE TRIGGER [ trigger_name | ALL | USER ]

    Enables a single trigger specified by trigger_name, enables all triggers, or enables only user triggers.

  • | ENABLE REPLICA TRIGGER trigger_name

    Determines that the trigger firing mechanism is affected by the configuration variable session_replication_role. When the replication role is origin (default value) or local, a simple trigger is fired.

    When ENABLE REPLICA is configured for a trigger, it is fired only when the session is in replica mode.

  • | ENABLE ALWAYS TRIGGER trigger_name

    Determines that all triggers are fired regardless of the current replication mode.

  • | DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE

    Enables or disables a rule for tables. Disabled rules are still visible in the system, but are not applied during query rewriting. The ON SELECT rule cannot be disabled because it is related to the view implementation. Rules configured as ENABLE REPLICA are enabled only when the session is in replica mode, while those configured as ENABLE ALWAYS can be enabled regardless of the replica mode. Rule triggering is also affected by configuration variables in session_replication_role, which is similar to the preceding trigger setting.

  • | DISABLE/ENABLE ROW LEVEL SECURITY

    Enables or disables row-level access control for a table.

    If row-level access control is enabled for a data table but no row-level access control policy is defined, the row-level access to the data table is not affected. If row-level access control for a table is disabled, the row-level access to the table is not affected even if a row-level access control policy has been defined. For details, see CREATE ROW LEVEL SECURITY POLICY.

  • | NO FORCE/FORCE ROW LEVEL SECURITY

    Forcibly enables or disables row-level access control for a table.

    By default, the table owner is not affected by the row-level access control feature. However, if row-level access control is forcibly enabled, the table owner (excluding system administrators) will be affected. System administrators are not affected by any row-level access control policies.

  • | ENCRYPTION KEY ROTATION

    Rotation of the transparent data encryption key.

    The data encryption key rotation of a table can be performed only when the transparent encryption function is enabled for the database and enable_tde of the table is set to on. After the key rotation operation is performed, the system automatically applies for a new KMS key. After the key rotation, the data encrypted using the old key is decrypted using the old key, and the newly written data is encrypted using the new key. To ensure the security of encrypted data, you can periodically update the key based on the amount of new data in the encryption table. It is recommended that the key be updated every two to three years.

  • INHERIT parent_table

    Adds the target data table to a specified parent data table as a new child data table. After that, the query for the parent data table will contain the data in the target data table. Before being added as a child data table, the target data table must contain all the columns in the parent data table. These columns must have matching data categories, and if they have NOT NULL constraints in the parent data table, they must also have NOT NULL constraints in the child data table. For all CHECK constraints in the parent data table, there must be corresponding constraints in the child data table, unless the parent data table is marked as non-inheritable.

  • NO INHERIT parent_table

    Generates the target data table from the child data table of a specified parent data table. Queries for the parent data table will no longer contain records generated from the target data table.

  • OF type_name

    Joins a table to a composite type, which is similar to table creation by using the CREATE TABLE OF option. The name and type of a table column must exactly match those defined in the composite type, but the OID system column can be different. The table cannot be inherited from any other table. These restrictions ensure that the CREATE TABLE OF option allows the same table definition.

  • NOT OF

    Removes the association between a table and a type.

  • REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

    Specifies the record level of old tuples in UPDATE and DELETE statements on a table in logical replication scenarios.

    • DEFAULT records the old value of the primary key column. If there is no primary key, DEFAULT does not record the old value.
    • USING INDEX records the old values of columns covered by the named indexes. These values must be unique, non-local, and non-deferrable, and contain the values of columns marked NOT NULL.
    • FULL records the old values of all columns in the row.
    • NOTHING does not record information in old rows.
    In logical replication scenarios, when the UPDATE and DELETE statements of a table are parsed, the parsed old tuples consist of the information recorded in this method. For tables with primary keys, this option can be set to DEFAULT or FULL. For a table without a primary key, set this parameter to FULL. Otherwise, the old tuple will be parsed as empty during decoding. You are not advised to set this parameter to NOTHING in common scenarios because old tuples are always parsed as empty.

    

    Even if DEFAULT or USING INDEX is specified, the old values of the columns in the current Ustore table may contain the old values of all columns in the row. This configuration option takes effect only when the old values involve TOAST values. For the Ustore table, the NOTHING option is invalid, and the actual effect is the same as that of FULL.

  • There are several clauses of column_clause:

    ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]    
    | MODIFY column_name data_type    
    | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
    | MODIFY column_name [ CONSTRAINT constraint_name ] NULL
    | DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]    
    | ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]    
    | ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }    
    | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL    
    | ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer    
    | ADD STATISTICS (( column_1_name, column_2_name [, ...] ))    
    | DELETE STATISTICS (( column_1_name, column_2_name [, ...] ))    
    | ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )    
    | ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )    
    | ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

    

    • ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]

      Adds a column to a table. If a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified).

    • ADD ( { column_name data_type [ compress_mode ] } [, ...] )

      Adds columns in the table.

    • MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] )

      Modifies the data type of an existing column in the table.

    • DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

      Drops a column from a table. Indexes and constraints related to the column are automatically dropped. If an object not belonging to the table depends on the column, CASCADE must be specified, such as a view.

      The DROP COLUMN statement does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent INSERT and UPDATE operations in the table will store a NULL value for the column. Therefore, column deletion takes a short period of time but does not immediately release the tablespace on the disks, because the space occupied by the deleted column is not recycled. The space will be recycled when VACUUM is executed.

    • ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

      Modifies the type of a column in a table. Indexes and simple table constraints on the column will automatically use the new data type by reparsing the originally supplied expression.

      ALTER TYPE requires an entire table be rewritten. This is an advantage sometimes, because it frees up unnecessary space from a table. For example, to recycle the space occupied by a deleted column, the fastest method is to use the following statement.

      ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

      In this statement, anycol indicates any column existing in the table and anytype indicates the type of the prototype of the column. ALTER TYPE does not change the table except that the table is forcibly rewritten. In this way, the data that is no longer used is deleted.

    • ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }

      Sets or removes the default value for a column. The default values only apply to subsequent INSERT operations; they do not cause rows already in the table to change. Defaults can also be created for views, in which case they are inserted into INSERT statements on the view before the view's ON INSERT rule is applied.

    • ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

      Changes whether a column is marked to allow null values or to reject null values. You can only use SET NOT NULL when the column contains no null values.

    • ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer

      Specifies the per-column statistics-gathering target for subsequent ANALYZE operations. The target can be set in the range from 0 to 10000. Set it to –1 to revert to using the default system statistics target.

    • {ADD | DELETE} STATISTICS ((column_1_name, column_2_name [, ...]))

      Adds or deletes the declaration of collecting multi-column statistics to collect multi-column statistics as needed when ANALYZE is performed for a table or a database. The statistics about a maximum of 32 columns can be collected at a time. You are not allowed to add or delete such declaration for system catalogs or foreign tables.

    • ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )

      ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )

      Sets or resets per-attribute options.

      Currently, the only defined per-attribute options are n_distinct and n_distinct_inherited. n_distinct affects statistics of a table, while n_distinct_inherited affects the statistics of the table and its subtables. Currently, only SET/RESET n_distinct is supported, and SET/RESET n_distinct_inherited is forbidden.

    • ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

      Sets the storage mode for a column. This clause specifies whether this column is held inline or in a secondary table, and whether the data should be compressed. It is set only for row-store tables and is invalid for column-store tables. If it is set for column-store tables, an error will be displayed when the statement is executed. SET STORAGE itself does not change anything in the table. It sets the strategy to be pursued during future table updates.

    • column_constraint is as follows:

      [ CONSTRAINT constraint_name ]
          { NOT NULL |
            NULL |
            CHECK ( expression ) |
            DEFAULT default_expr  |
            GENERATED ALWAYS AS ( generation_expr ) STORED |
            UNIQUE index_parameters |
            PRIMARY KEY index_parameters |
            ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |                                                                                  
            REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
                [ ON DELETE action ] [ ON UPDATE action ] }    [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

    • compress_mode of a column is as follows:

      [ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS ]

  • table_constraint_using_index used to add the primary key constraint or unique constraint based on the unique index is as follows:

    [ CONSTRAINT constraint_name ]
        { UNIQUE | PRIMARY KEY } USING INDEX index_name
        [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

  • table_constraint is as follows:

    [ CONSTRAINT constraint_name ]
        { CHECK ( expression ) |
          UNIQUE ( column_name [, ... ] ) index_parameters |
          PRIMARY KEY ( column_name [, ... ] ) index_parameters |
          PARTIAL CLUSTER KEY ( column_name [, ... ]  }
          FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
             [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
        [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

    index_parameters is as follows:

    [ WITH ( {storage_parameter = value} [, ... ] ) ]
        [ USING INDEX TABLESPACE tablespace_name ]

Parameter Description

• IF EXISTS

  Sends a notice instead of an error if no tables have identical names. The notice prompts that the table you are querying does not exist.

• table_name [*] | ONLY table_name | ONLY ( table_name )

  table_name is the name of the table that you need to modify.

  If ONLY is specified, only the table is modified. If ONLY is not specified, the table and all subtables are modified. You can add the asterisk (*) option following the table name to specify that all subtables are scanned, which is the default operation.

• constraint_name

  Specifies the name of an existing constraint to drop.

• index_name

  Specifies the name of an index.

• storage_parameter

  Specifies the name of a storage parameter.

  TDE options:

  • enable_tde (bool type)

    Whether to enable TDE for a table. Before enabling this function, ensure that enable_tde in "GUC Parameter" has been enabled, KMS has been enabled, and the database instance master key ID in tde_cmk_id in "GUC Parameter" has been correctly configured.

    This parameter supports only row-store tables. Column-store tables and temporary tables are not supported. The Ustore storage engine is not supported. This parameter can be modified only when enable_tde is specified during table creation. Switching the encryption switch status does not change the encryption algorithm and key information.

    Value range: on and off on indicates that transparent data encryption is enabled. After the value is switched from off to on, new data is automatically encrypted when being written to the data page, and old data is automatically encrypted when the data page is updated. off indicates that transparent data encryption is disabled. After the value is switched from on to off, newly written data is not encrypted, old encrypted data can be automatically decrypted when being read, and data is not encrypted when being written back to the data page.

    Default value: off

  The following option is added for creating an index:

  • parallel_workers (int type)

    Number of bgworker threads started when an index is created. For example, value 2 indicates that two bgworker threads are started to create indexes concurrently.

    Value range: [0,32]. The value 0 indicates that this function is disabled.

    Default value: If this parameter is not set, the concurrent index creation function is disabled.

  • hasuids (Boolean type)

    Default value: off

    If this parameter is set to on, a unique table-level ID is allocated to a tuple when the tuple is updated.

  The following option is added to fix optimizer statistics:

  • min_tuples (float8)

    Default value: 0

    The optimizer selects the larger value of the estimated statistics and the parameter to calculate the data volume based on the estimation table of statistics.

• new_owner

  Specifies the name of the new table owner.

• new_tablespace

  Specifies the new name of the tablespace to which the table belongs.

• column_name, column_1_name, column_2_name

  Specifies the name of a new or existing column.

• data_type

  Specifies the type of a new column or a new type of an existing column.

• compress_mode

  Compression option of a table field. The clause specifies the compression algorithm preferentially used by the column. Row-store tables do not support compression.

• collation

  Specifies the collation rule name of a column. The optional COLLATE clause specifies a collation for the new column; if omitted, the collation is the default for the new column. You can run the select * from pg_collation; command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result.

• USING expression

  Specifies how to compute the new column value from the old; if omitted, the default conversion is an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from the old to new type.

  

  USING in ALTER TYPE can specify any expression involving the old values of the row; that is, it can refer to any columns other than the one being cast. This allows general casting to be done with the ALTER TYPE syntax. Because of this flexibility, the USING expression is not applied to the column's default value (if any); the result might not be a constant expression as required for a default. This means that when there is no implicit or assignment cast from old to new type, ALTER TYPE might fail to convert the default even though a USING clause is supplied. In such cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and then use SET DEFAULT to add a suitable new default. Similar considerations apply to indexes and constraints involving the column.

• NOT NULL | NULL

  Sets whether the column allows null values.

• integer

  Specifies the constant value of a signed integer. When using PERCENT, the range of integer is from 0 to 100.

• attribute_option

  Specifies an attribute option.

• PLAIN | EXTERNAL | EXTENDED | MAIN

  Specifies a column-store mode.

  • PLAIN must be used for fixed-length values (such as integers). It must be inline and uncompressed.
  • MAIN is for inline, compressible data.
  • EXTERNAL is for external, uncompressed data. Use of EXTERNAL will make substring operations on text and bytea values run faster, at the penalty of increased storage space.
  • EXTENDED is for external, compressed data. EXTENDED is the default for most data types that support non-PLAIN storage.
• CHECK ( expression )

  New rows or rows to be updated must satisfy for an expression to be true. If any row produces a false result, an error is raised and the database is not modified.

  A check constraint specified as a column constraint should reference only the column's values, while an expression in a table constraint can reference multiple columns.

  Currently, CHECK ( expression ) does not include subqueries and cannot use variables apart from the current column.

• DEFAULT default_expr

  Assigns a default data value to a column.

  The data type of the default expression must match the data type of the column.

  The default expression will be used in any insert operation that does not specify a value for the column. If there is no default value for a column, then the default value is null.

• GENERATED ALWAYS AS ( generation_expr ) STORED

  This clause creates a column as a generated column. The value of the generated column is calculated by generation_expr when data is written (inserted or updated). STORED indicates that the value of the generated column is stored as a common column.

  

  • The generation expression cannot refer to data other than the current row in any way. The generation expression cannot reference other generation columns or system columns. The generation expression cannot return a result set. No subquery, aggregate function, or window function can be used. The function called by the generation expression can only be an immutable function.
  • Default values cannot be specified for generated columns.
  • The generated column cannot be used as a part of the partition key.
  • Do not specify the generated column and the CASCADE, SET NULL, and SET DEFAULT actions of the ON UPDATE constraint at the same time. Do not specify the generated column and the SET NULL, and SET DEFAULT actions of the ON DELETE constraint at the same time.
  • The method of modifying and deleting generated columns is the same as that of common columns. Delete the common column that the generated column depends on. The generated column is automatically deleted. The type of the column on which the generated column depends cannot be changed.
  • The generated column cannot be directly written. In the INSERT or UPDATE statement, values cannot be specified for generated columns, but the keyword DEFAULT can be specified.
  • The permission control for generated columns is the same as that for common columns.
  • Columns cannot be generated for column-store tables and memory-optimized tables (MOTs). In foreign tables, only postgres_fdw supports generated columns.
• UNIQUE index_parameters

  UNIQUE ( column_name [, ... ] ) index_parameters

  Specifies that a group of one or more columns of a table can contain only unique values.

• PRIMARY KEY index_parameters

  PRIMARY KEY ( column_name [, ... ] ) index_parameters

  Specifies that a column or columns of a table can contain only unique (non-duplicate) and non-null values.

• REFERENCES reftable [ ( refcolum ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint)

  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (table constraint)

  The foreign key constraint requires that the group consisting of one or more columns in the new table should contain and match only the referenced column values in the referenced table. If refcolum is omitted, the primary key of reftable is used. The referenced column should be the only column or primary key in the referenced table. A foreign key constraint cannot be defined between a temporary table and a permanent table.

  There are three types of matching between a reference column and a referenced column:

  • MATCH FULL: A column with multiple foreign keys cannot be NULL unless all foreign key columns are NULL.
  • MATCH SIMPLE (default): Any unexpected foreign key column can be NULL.
  • MATCH PARTIAL: This option is not supported currently.

  In addition, when you perform certain operations on the data in the referenced table, the operations are performed on the corresponding columns in the new table. ON DELETE: specifies the operations to be executed after a referenced row in the referenced table is deleted. ON UPDATE: specifies the operation to be performed when the referenced column data in the referenced table is updated. The possible actions of the ON DELETE and ON UPDATE clauses are as follows:

  • NO ACTION (default): When a foreign key is deleted or updated, an error indicating that the foreign key constraint is violated is created. If the constraint is deferrable and there are still any referenced rows, this error will occur when the constraint is checked.
  • RESTRICT: When a foreign key is deleted or updated, an error indicating that the foreign key constraint is violated is created. It is the same as NO ACTION except that the action cannot be delayed.
  • CASCADE: deletes any row that references the deleted row from the new table, or update the field value of the referenced row in the new table to the new value of the referenced column.
  • SET NULL: sets the referenced field to NULL.
  • SET DEFAULT: sets the referenced columns to their default values.
• DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE

  Sets whether the constraint can be deferrable.

  • DEFERRABLE: deferrable to the end of the transaction and checked using SET CONSTRAINTS.
  • NOT DEFERRABLE: checks immediately after the execution of each command.
  • INITIALLY IMMEDIATE: checks immediately after the execution of each statement.
  • INITIALLY DEFERRED: checks when the transaction ends.
    

    Ustore tables do not support the DEFERRABLE and INITIALLY DEFERRED constraints.

• PARTIAL CLUSTER KEY

  Specifies a partial cluster key for storage. When importing data to a column-store table, you can perform local data sorting by specified columns (single or multiple).

• WITH ( {storage_parameter = value} [, ... ] )

  Specifies an optional storage parameter for a table or an index.

• tablespace_name

  Specifies the name of the tablespace where the index locates.

• COMPRESS|NOCOMPRESS
  • NOCOMPRESS: If the NOCOMPRESS keyword is specified, the existing compression feature of the table will not be changed.
  • COMPRESS: If the COMPRESS keyword is specified, the table compression feature will be triggered by batch tuple insertion. Row-store tables do not support compression.
• new_table_name

  Specifies the new table name.

• new_column_name

  Specifies the new name of a specific column in a table.

• new_constraint_name

  Specifies the new name of a table constraint.

• new_schema

  Specifies the new schema name.

• CASCADE

  Automatically drops objects that depend on the dropped column or constraint (for example, views referencing the column).

• RESTRICT

  Refuses to drop the column or constraint if there are any dependent objects. This is the default processing.

• schema_name

  Specifies the schema name of a table.

Examples

See Examples in CREATE TABLE.

Helpful Links

CREATE TABLE and DROP TABLE