check constraint

[clinton]

How do you modify a check constraint?
I have 5 check constraints on a column. I want to add 2 more constraints to the column to make the total check constraints on that column 7.
How do I do that?

[jgmagnus]

alter table add constraint

so:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (condition);

okay?

- Magnus

[clinton]

You miss read my question.

I already have this check constraint:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (column_name in (1,2,3,4,5,));

Now I would like to add 7and 8 to it(so that it would look like this 1,2,3,4,5,6,7,8) how do I do that?

[jdorlon]

Just drop and recreate the constraint,
like this :

alter table table_name
drop contstraint constraint_name;

alter table table_name
add constraint constraint_name
.....;

[kmesser]

alter table table_name drop constraint my_constraint;

alter table table_name add constraint my_constraint(check mycol in (1,2,3,4,5,6,7,8));

[clinton]

If the table is loaded with data, would there be a problem dropping and recreating the check constraint?

[roger]

Hmmm, not really a problem but:

Between the drop and the recreate no check constraint is active...

When recreating the check constraint the table is locked until all the data is checked again...

Roger