To Foreign Key or not to Foreign Key..... (inet -> cidr)

Question

Below is a very abridged version of a project I am working on.

CREATE TABLE cidr_block (
    id serial not null unique,
    block_def cidr primary key,
    dhcp_server_id int not null references computer_system (id),
    ...
);
CREATE TABLE ip_addr (
    computer_id int not null references computer_system (id),
    mac_addr macaddr not null unique,
    ip_addr inet not null unique,
);

This is a network configuration database. Now, it is expected that most, but not all, ip_addr.ip_addr records will be in cidr_block.block_def blocks. Others, which might be externally linked vpn interfaces to customer locations might not be. There is no simple, declarative way to ensure a fkey for the applicable rows between the two tables. So I am wondering:

1. Is it preferable to use triggers to create fkey links where appropriate?

2. Is it sufficient just to join where expected? If a computer is given a bad IP address, it just won't be given one from the dhcp server, and we will know this pretty quickly. (writes trigger notifications which trigger updates on relevant dhcp servers)

3. Is there another, better way?

Answer 1

To answer my own question here, here is what I eventually did:

1. Function ip_block(inet) to look up the ip block record of an inet address.

2. Check constraint to make sure that returns not null for appropriate classes of addresses. That would be triggered on insert or update to the assignments table.

3. Trigger function to check assignments table for addresses in a block and raise an RI exception if such are found.

4. Trigger which runs the above function when the block_def of the ip block is updated, or the ip block is deleted.

I will leave this answer unaccepted for a while to see if anyone else has a better approach. However the amount of code to actually do this turned out to be a lot less than expected so I am pretty happy with it so far.