Using this command you can define, delete, or modify constraints.
You need to have the system privilege ALTER ANY TABLE, object privilege ALTER on the table or its schema, or the table belongs to the current user or one of the user roles.
- 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.
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.
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 in the system tables EXA_USER_CONSTRAINTS, EXA_ALL_CONSTRAINTS, and EXA_DBA_CONSTRAINTS, the metadata for NOT NULL constraints in EXA_USER_CONSTRAINT_COLUMNS, EXA_USER_CONSTRAINT_COLUMNS, and EXA_DBA_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).