ALTER TABLE (Constraints)

Purpose

Using this command you can define, delete, or modify constraints.

Prerequisites

  • You need either the system privilege USE ANY SCHEMA or the object privilege USAGE on the target schema, or the schema must be owned by you or one of your assigned roles.

  • You need either the system privilege ALTER ANY TABLE or the object privilege ALTER on the table or its schema, or the table must be owned by you or one of your assigned roles.

Syntax

alter_table_constraint::=

ALTER TABLE Constraints

out_of_line_constraint::=

Out of Line Constraint

Out of Line Constraint

references_clause::=

Reference Clause

Usage Notes

  • The following constraints can be defined for columns: 
Constraints Description
PRIMARY KEY

All values have to be unique , NULL values are not allowed. A table may only have one primary key.
FOREIGN KEY

A foreign key always references the primary key of a second table. The column content must either exist in the primary key column or must be NULL (in case of a composite key in all of its columns). The datatype of a foreign key and its corresponding primary key must be identical. Foreign keys cannot reference a virtual object.
NOT NULL

No NULL values can be inserted. A NOT NULL constraint can only be specified either directly in the table definition or by the ALTER TABLE MODIFY COLUMN statement.
  • Constraints can have a name for easier identification and always have one of the following two states:
Constraints Description
ENABLE The constraint is directly checked after DML statements (see Manipulation of the database (DML) for more information). This process costs some time but ensures the data integrity.
DISABLE This state can be usefully if you want to define the metadata within the database, but avoid a negative performance impact.

If no explicit state is defined, then the session parameter CONSTRAINT_STATE_DEFAULT is used (see ALTER SESSION for more information). The current setting of this parameter can be found in the system table EXA_PARAMETERS.

Examples

ALTER TABLE t1 ADD CONSTRAINT my_primary_key PRIMARY KEY (a);
ALTER TABLE t2 ADD CONSTRAINT my_foreign_key FOREIGN KEY (x) REFERENCES t1;
ALTER TABLE t2 MODIFY CONSTRAINT my_foreign_key DISABLE;
ALTER TABLE t2 RENAME CONSTRAINT my_foreign_key TO my_fk;
ALTER TABLE t2 DROP CONSTRAINT my_fk;