ALTER Definition (alter_definition) 

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

Syntax

<alter_definition> ::=
  ALTER CONSTRAINT <constraint_name> CHECK <search_condition>
| ALTER <key_definition>

Examples

SQL-Tutorial, Primary Key, Constraints

Explanation

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

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 more time, particularly with longer tables, as a large amount of copying needs to be done.