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
