How to add complicated CHECK constraints to ALTER table command?

[zxmgh]

Hi, all:

I am trying to add three columns to a table(It is a production database so I can't recreate it) by using ALTER TABLE command:

ALTER TABLE order ADD
(
start_date DATE,
end_date DATE,
check_flag NUMBER(1) DEFAULT 1
);

and a want to add two CHECK constraints to the table,

One is:

ALTER TABLE order
ADD (CONSTRAINT Check_flag_CC
CHECK(check_flag IN (0,1)));
-- To make sure the value of check_flag is either 0 or 1

The other is to make sure
1. Both start_date and end_date are NOT NULL when the value of check_flag is 0 and
2. start_date < end_date when the value of check_flag is 0,

I am not sure how to do it, add another CHECK constraint? any ideas?

Thanks a lot!

[stecal]

You can use a trigger to check for these conditions. What you are trying to check is a business rule (if you want to look at it this way), and a trigger will definitely accomplish this for you.

[zxmgh]

You are quite right, trigger can do it. But the problem is that I don't have the create trigger privilege and the senior DBA who can grant this privilege to me is very picky and it is the last thing I would do.

Anyway, I guess I have to find other way.

[jmodic]

There is absolutely no need to use triggers when you can do it declaratively with database constraints. It is much more efficient and less volatile that way.

You can add another check constraint to incorporate that additional bussiness logic, there is no limitation of how many check constraints you can have on a single table.

But I would rather drop your existing constraint and incorporate the whole logic in a single check constraint.

ALTER TABLE oreder DROP CONSTRAINT check_flag_CC;

ALTER TABLE order ADD CONSTRAINT Check_flag_and_dates 
CHECK (check_flag = 1
       OR (check_flag = 0 AND start_date IS NOTNULL AND end_date IS NOT NULL AND start_date < end_date)
      );