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:
Is it preferable to use triggers to create fkey links where appropriate?
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)
Is there another, better way?