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::=
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 , 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).