> I guess that rule can be achieved with triigers on TableA and TableC - but > the same is true for FK (and FK constraint is more effective then trigger > - > that is why I wonder would it be useful/achievable to create that kind of > constraint) > > Thoughts, ideas?
You create a common "keys in use" table and only insert a record into the main tables if you can successfully add the desired key to the shared keys table ( as a unique value ). Setup a normal FK to that table to help enforce that valid records must exist on the keys table. Not fool-proof but you only need to worry about insertions - delete from the pk table to remove the record from the main table and free up the key.
David J.
Thanks David,
Yes, that is one of ways that goal can be achieved via triggers (or to let someone else worry about that Key is inserted/updated/deleted in Master Table first...)
Constraint - should be more effective way... (It shouldnt be mixed with FK constraint - even it is opposite on some kind... - it was just simplest way to describe the feature)
And it should ensure that every row in table is valid from moment it is created (what trigger can't ensure - constraint does it - or constraint cant be created etc)
