Constraints

[mariec]

Hi,

How can you create column constraints when designing a database model and get this constraint to be created when generating the DDL file?

For example creating a CHECK constraint where the column value must be between 0 and 20.

thanks,

[David ODriscoll]

Marie

I needed to do the same this afternoon.  After some experimentation, I found that you need to do the following:

1.  Create a new operation on the target table
2.  Set the stereotype to "check"
3.  Select the column affected (eg Status)
4.  Put the condition in the "Notes" field on the General tab.  For example, "IN (1,2,3)"

This yields DDL of:
ALTER TABLE <table>
ADD CONSTRAINT CK_<table>_<column> CHECK (Status IN (1, 2, 3))

I had trouble using the BETWEEN clause, but maybe this will help you track down the solution.  If so, please let me know.

Regards
David O'Driscoll
Brisbane, Australia

[mariec]

Thanks! It works fine. I followed your exact explaination and I got this:

ALTER TABLE <table>
ADD CONSTRAINT <constraint_name> CHECK (<column> BETWEEN 0 AND 20);

Just didn't think the "notes" field was used for this type of operation.

Thanks again, this saves me from doing constraints manually!

[sorayanasar]

Hi everybody!

I've done what you said about the check constraints and it worked fine! But now I have to make the same thing but with multiple fields. I mean, one constraint that checks something like this:

(FIELD_A IS NULL AND FIELD_B IS NOT NULL AND FIELD_C IS NOT NULL) OR
(FIELD_A IS NOT NULL AND FIELD_B IS NULL AND FIELD_C IS NULL)

In fact, I've already done it in this way:
1) create the constraint
2) setting the above clause in the notes but without setting any field in the columns tab.

Is there another way to do that?
Thanks!!!
Soraya

[mariec]

Hi,

you can follow the same steps as decribed above, but skip the "column selection" part. Just put the check condition in the notes part and include the column names in there (exactly like your example).

It should give you something like this:

ALTER TABLE TestTable
ADD CONSTRAINT CK_Test CHECK ( (FIELD_A IS NULL AND FIELD_B IS NOT NULL AND FIELD_C IS NOT NULL) OR (FIELD_A IS NOT NULL AND FIELD_B IS NULL AND FIELD_C IS NULL))
;