Re: Checking for changes in other tables - Mailing list pgsql-general

I have two tables with countries and persons living in those countries:

    create table countries (
        code        char(2)     not null primary key,
        eu          boolean     not null
    );

    insert into countries values
        ('AR', false),
        ('BE', true),
        ('CH', false),
        ('DE', true);

    create table persons (
        name        text        not null primary key,
        country     char(2)     not null references countries(code)
    );

    insert into persons (name, country) values
        ('Arthur',  'AR'),
        ('Betty',   'BE'),
        ('Charlie', 'CH'),
        ('Diane',   'DE');

Enter a third table for loans that can only be made between persons
living in EU countries:

    create table eu_loans (
        donor       text        not null references persons(name),
        recipient   text        not null references persons(name),
        primary key (donor, recipient)
    );

    insert into eu_loans (donor, recipient) values
        ('Diane', 'Betty');

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.

At the moment, this is checked by the application, but not enforced by
the database. I could add more triggers to the persons table (and
another one on countries), but that doesn't "feel" right... countries
and persons are base data and shouldn't need to "know" about other
tables using their records. Ideally, eu_loans would have a check
constraint to verify that its contents remain valid.

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?

Thanks for any suggestions.

crl


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general