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 privilegeUSAGE
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 privilegeALTER
on the table or its schema, or the table must be owned by you or one of your assigned roles.
Syntax
alter_table_constraint::=
out_of_line_constraint::=
references_clause::=
Usage Notes
- The following constraints can be defined for columns:
Constraints | Description |
---|---|
PRIMARY KEY |
All values have to be unique , |
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 |
NOT NULL |
No |
- 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).