Hi everyone;
I have a project right now where functional foreign keys would be very
handy. Let me explain the specific use case I am looking at and I would
assume that there are many more.
I am looking at building a network configuration database for virtualized
machines in a hosting environment. Now if I had functional foreign keys I
would probably build the db differently (I will probably simulate a partial
functional foreign key with a check constraint, a custom function, and a
custom trigger, but DRI would be helpful here for both the partial and
functional aspects). The partial aspects can be done today using table
inheritance (with some caveats) but functional foreign keys are not
supported.
I have a table which tracks CIDR assignments to DHCP servers, and another
table which tracks IP address to MAC assignments. There are some
complications here regarding the fact that not all IP addresses are managed
via DHCP, but this could be managed....
What would be nice to be able to do is to be able to do something like:
ALTER TABLE inet_assignment ADD FOREIGN KEY (network(inet_address))
REFERENCES cidr_block(block_def);
There are some obvious limitations here. A foreign key could only use
immutable functions, for example. Additionally if we need to handle
partial foreign keys we could do so with an immutable function returning
NULL from the table type based on relevant criteria. Also there are a
couple possible dependency issues, such as:
1: The foreign key depends on the function so the function cannot be
dropped first absent CASCADE
2: If the function is redefined, one would have to check all rows to verify
that they meet the new function's requirements. This could pose a
performance issue with DDL.
There are obvious workarounds. One could use a trigger and a foreign key.
But my questions are:
1. Is there enough use in something like this to even try to tackle it?
2. Are there any other major showstoppers I haven't thought of?
Best Wishes,
Chris Travers