Thanks for the repsonse. As far as I've seen, there is no such thing in the SQL Standards. I surely see your points, and in fact it is kind of the intention too allow a single column represent several types of entities. It would be a nice feature for generalization. We may have the following scenario: We have 20+ tables for which changes should be audited. Referential integrity should be enforced. How can we solve this today?
1. A dedicated audit table for each table which should be audited.
2. A singe audit table, and a link table with one (sparse) column for each table which should be audited, and a check constraint enforcing only one of these being filled out.
3. A single audit table, and triggers to enforce referential integrity.
In my opinion, even though it is not in the SQL Standard, a kind of filtered foreign key would be a cleaner solution than any of these.
We could also think of the following: A system with the concept of open/closed, active/inactive or something similar. Open orders have all their data in a set of table, but closed orders have some data moved to different tables for perforamnce reasons since they're seldom queried.
So, all of this can
be solved in other ways, but personally I would have liked to have a filtered foreign key.
Finally... As for standards, everything in the standard has as some time not been in the standard, either because the standard did not exist or because it was a new and (hopefully) bright idea.
Ole Kristian Velstadbråten Bangås - Virinco
- TwitterConcatenating Row Values in Transact-SQL