Re: Checking for changes in other tables

On Fri, Apr 26, 2013 at 11:01:28AM +0200, CR Lender wrote:

> Is there any way to ensure that all donors and recipients in eu_loans
> are in the EU, without altering the countries and persons tables?

One way to do this would be to add countries to the eu_loans table so
it looks like this:

create table eu_loans (
donor text not null,
donor_country char(2) not null,
recipient text not null,
recipient_country char(2) not null,
primary key(donor, recipient),
foreign key (donor, donor_country) references persons (name, country) on update cascade,
foreign key (recipient, recipient_country) references persons (name, country) on update cascade
);

then create an sql function to tell you whether a country is in the eu:

create function country_in_eu (char(2)) returns bool as $$
select count(*) > 0 from countries where code = $1 and eu = 't'
$$ language 'sql';

and add two constraints to eu_loans:

alter table eu_loans add constraint donor_in_eu check(country_in_eu(donor_country));
alter table eu_loans add constraint recipient_in_eu check(country_in_eu(recipient_country));

This will give an error if someone moves outside the EU (but not if a
country leaves the EU).

It may or may not seem elegant depending on your thinking but it does
have the effect you're looking for. Of course you could set things up
so that you could do an insert to eu_loans specifying just the donor
and recipient names and the system would populate the country fields
for you by looking up in persons, throwing an error if appropriate.

Richard