2013/9/19 David Johnston <pol...@yahoo.com> > Misa Simic wrote > > 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) Thanks, Misa