AW: Constraint based upon a separate file

No I didn't - Foreign key constraints or referential integrities are a
different animal.
The original post was about check constraints. And in check constraints you
cannot reference to anything else than the same row in the same table.

The following is are valid check constraints:
MyStatus in ('A', 'B', 'C')

OrderDate < DeliveryDate (assumed OrderDate and DeliveryDate are both
columns in the table)

But it is not allowed to replace the IN-List with a sub-select, i.e. testing
for valid stati in a different table.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Dennis Lovelady
Gesendet: Thursday, 20.9 2012 11:43
An: 'Midrange Systems Technical Discussion'
Betreff: RE: Constraint based upon a separate file

I must not understand what you mean, Birgitta.

Are you saying that DB2 does not support the following?

alter table my_table add foreign key (your_table_column) references
your_table

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"We're proud of our restaurant. The public is invited to inspect at any
time. KEEP OUT!"
-- sign on kitchen door of a New Your City restaurant

A constraint can only compare columns or values within the same row in
the same table.
If you need to compare a value with values located in other tables,
you may write a before insert/update triggers that compares the
incoming value with the allowed values and returns an error if the
value does not match.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
rpglist@xxxxxxxxxxx
Gesendet: Wednesday, 19.9 2012 23:27
An: midrange-l@xxxxxxxxxxxx
Betreff: Constraint based upon a separate file

I know how to add a typical check constraint where it ensures that the
value matches a short list of codes, etc. I have been searching for
the past few hours to no avail for an example that shows how to add a
check constraint based upon the value in a separate file:

Quick and dirty example would be a state field of two characters that
has to match a predfined table of state abbrev's or a zipcode lookup
table...

Does anyone have any examples I can reference? I have been all over
the reference manual and so far haven't found what I'm looking for.

Thanks,

C

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.