|
|
Oracle ALTER TABLE Constraint:
Syntax examples
Oracle Database Tips by Donald Burleson |
We have "alter table" syntax from
Oracle to add
data constraints in-place in this form:
alter table
table_name
add constraint
constraint_name;
We can also use "alter table" syntax to enable or disable
constraints:
alter table
table_name
ENABLE constraint
constraint_name;
alter table
table_name
DISABLE constraint
constraint_name;
Check Constraint
We have details on the different types of constraints:
alter table
table_name
add constraint
check_constraint_name
CHECK
(check_column_name IN
(
'check_constraint1_value',
'check_constraint2_value',
'check_constraint3_value',
'check_constraint4_value'
)
) DISABLE|ENABLE;
Here are some examples of Oracle "alter table"
syntax to add foreign key constraints.
alter table
cust_table
add constraint
fk_cust_name FOREIGN KEY (person_name)
references
person_table (person_name)
initially deferred deferrable;
Here is an example of a multiple column foreign key constraint:
alter table
cust_table
add constraint
fk_cust_name FOREIGN KEY (person_name, person_gender)
references
person_table (person_name, person_gender)
initially deferred deferrable;
Here is another example of Oracle "alter table" syntax to
drop constraints.
ALTER TABLE
cust_table
drop constraint
fk_cust_table_ref;
Here we use Oracle "alter table" syntax to add a check constraint.
alter table
cust_table
add constraint
check_cust_types
CHECK
(cust_type IN
(
'yuppie',
'dink',
'guppie'
)
);
Oracle
Constraint Errors
The following
errors are associated with Oracle constraint alter commands:
-
ORA-02290: check constraint (owner.constraintname) violated
-
ORA-02291: integrity constraint (owner.constraintname) violated
- parent key not found
-
ORA-02292:violated integrity constraint (owner.constraintname)-
child record found
Information on Boolean Expressions in Check
Contraints is available at the following:
Boolean Expressions in Check Constraints
|