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.

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.

• You can find the corresponding metadata for primary and foreign key constraints and NOT NULL constraints in the system tables EXA_ALL_CONSTRAINTS and EXA_ALL_CONSTRAINT_​COLUMNS.

  If no explicit name was specified, the system implicitly generates a unique name.

• Constraints can also be defined directly within the CREATE TABLE statement and can be modified by the command ALTER TABLE (Constraints).

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;