Show TOC

Background documentationALTER Definition (alter_definition) Locate this document in the navigation structure

 

By specifying an ALTER definition (alter_definition) in the ALTER TABLE statement, you can change a CONSTRAINT definition or a key definition.

Structure

Syntax Syntax

  1. <alter_definition> ::=
      ALTER CONSTRAINT <constraint_name> CHECK <search_condition>
    | ALTER <key_definition>
End of the code.
Examples

SQL-Tutorial, Primary Key, Constraints

Explanation

By specifying an ALTER definition you can change a CONSTRAINT definition or a key definition.

Changing a CONSTRAINT Definition: ALTER CONSTRAINT <constraint_name>

The constraint name must identify a CONSTRAINT definition in the table. If the specified search condition is not violated by any row in the table, it replaces the existing search condition of the CONSTRAINT definition. Otherwise, the ALTER TABLE statement fails.

Changing a Key Definition: ALTER <key_definition>

The key specified by the key definition replaces the current key in the table. The columns specified in the key definition must identify columns in the table and have the key property. No key column can therefore have a NULL value and no two rows in the table may have the same values in all key columns.

If a column of the key to be replaced is a referenced column (referenced_column) of a referential CONSTRAINT definition, the ALTER TABLE statement fails.

This may take some time, particularly with longer tables, since extensive copy operations are carried out.