column_constraint_clause
The following is the syntax for the column_constraint_clause in the ALTER TABLE statement:
{
[NULL | NOT NULL] |
[UNIQUE | PRIMARY KEY] |
[FOREIGN KEY (column [, column ...] )]
[REFERENCES [schema.]table_name[(column)] [ON DELETE CASCADE] ]
[CHECK (condition) ]
}
[USING INDEX
[PCTFREE integer]
[PCTUSED integer]
[INITRANS integer]
[MAXTRANS integer]
[TABLESPACE tablespace_name]
[STORAGE (
[INITIAL integer [K | M]]
[NEXT integer [K | M]]
[MINEXTENTS integer [K | M]]
[MAXEXTENTS {integer | UNLIMITED}]
[PCTINCREASE integer]
[FREELISTS integer]
[FREELIST GROUPS integer]
)
[NOSORT]
[LOGGING | NOLOGGING]
]
[EXCEPTIONS INTO [schema.]table_name]
[{ENABLE [VALIDATE | NOVALIDATE] | DISABLE} ]
[[NOT] DEFERRABLE [INITIALLY {IMMEDIATE | DEFERRED}] ]
[[INITIALLY {IMMEDIATE | DEFERRED}] [[NOT] DEFERRABLE] ]- NULL
Specifies that the values in the column list may contain NULL.
- NOT NULL
Specifies that the values in the column may not contain NULL.
- UNIQUE
- PRIMARY KEY
Specifies that the column list will be a primary key. A primary key can be referenced from another table with a foreign key. A primary key must also be UNIQUE and NOT NULL.
- FOREIGN KEY
Requires that all values in the column list must be either NULL or found in the referenced table’s defined primary key or the specified column list.
- REFERENCES
- CHECK
Allows you to specify an expression that a column value must satisfy.
- USING INDEX
Specifies physical characteristics of the index created to support the UNIQUE or PRIMARY KEY constraint. The index will always be created.
- PCTFREE
Specifies the percentage of space to be reserved in each data block for future updates to rows contained in that block. Valid values are - 99, and the default value is 10.
- PCTUSED
Specifies the minimum percentage of space that will be maintained as used in each data block. Valid values are 1- 99, and the default value is 40.
- INITRANS
Specifies the initial number of transaction entries allocated to each block.
- MAXTRANS
Specifies the maximum number of transaction entries allocated to each block. Specify UNLIMITED for unlimited expansion.
- TABLESPACE
Specifies the name of the tablespace where this object will be stored. If omitted, the default tablespace for the schema owner will be used.
- STORAGE
Specifies the physical storage characteristics of this object. For keyword descriptions, see the STORAGE clause described earlier for this command.
- NOSORT
Specifies that rows have been inserted into the database in sequential order; thus, no sorting is required when creating the index.
- LOGGING
Specifies that redo log records will be written during index creation.
- NOLOGGING
Specifies that redo log records will not be written during index creation. In case of a database failure, the index cannot be recovered by applying log files, and must be recreated. This option will speed the creation of indexes.
- EXCEPTIONS INTO
Specifies the table to list the ROWIDs that violate the constraint at the time the constraint is enabled.
- ENABLE
Specifies that the constraint is to be enabled at creation time.
- DISABLE
Specifies that the constraint is to be defined, but not initially enabled.
- DEFERRABLE
Specifies that DML statements can be executed that violate the constraint as long as the constraint is enforceable by the time of the commit.
- NOT DEFERRABLE
Specifies that the constraint is checked with each DML statement.
- INITIALLY IMMEDIATE
Specifies that even though the constraint can be deferred, it is initially not deferred.
- INITIALLY DEFERRED
Specifies for deferrable constraints that the constraint is deferred initially.