Re: Checking for changes in other tables

On 2013-04-26 12:17, D'Arcy J.M. Cain wrote:
> On Fri, 26 Apr 2013 11:01:28 +0200
> CR Lender <crlender(at)gmail(dot)com> wrote:

>> create table countries (
>> code char(2) not null primary key,
>
> Isn't this redundant? Primary keys are always NOT NULL.

Yes, I forgot to remove the NOT NULL when I adjusted the example.

> Side question - are you really limiting them to one loan each? Can't a
> donor have two active loans with the same recipient?

This is just a very reduced example structure (I wouldn't make a
person's first name the primary key, either :-). The actual case doesn't
even involve persons or loans, but it's far too complex to be used as an
example. It took weeks for me to understand how everything in that
database was (supposed to be) connected.

>> I can add a trigger on eu_loans to check if Diane and Betty both live
>> in the EU. The problem is how to prevent one of them from moving to a
>> non-EU country (if they do, the loan has to be cancelled first). They
>> are however allowed to move to other EU countries.
>
> Wouldn't two constraints, one for each of donor and recipient, do the
> job? Moving a person out of the EU would have the same effect as
> deleting them. The constraint would prevent it.

I'm not sure I'm following... how would such a constraint look like?

Thanks,
crl