Re: Why is a REFERENCES privilege required? And is it "better" to create FK relationship only between tables in the same schema?

Mark, you wrote:
> When the object owner, userA, grants userB, REFERENCES, userA has in
> effect given userB the right to stop userA from updating their own
> table unless userA's action meets the requirement imposed by
> userB's FK.

Thanks Mark. Wouldn't it be "more correct" or more complete to add to the above (although it could be deduced):

... if and only if userB then proceeds to create a foreign key referencing userA's table? UserA granting REFERENCES to userB doesn't auto-generate a FK in userB's schema from userB's table to userA's table. It's just a pre-condition for userA electing to do generate that key, and volunteering, as it were, to be so constrained. Unless I've misunderstood you--quite possible. As the OP, I'm the confused one here by definition. :-)

Just wanted to make things as clear as possible for myself and for posterity.

> In a large application where multiple owners are definded for areas/ departments you would expect that the need for FK
> to reference other owners tables will exist.

Makes sense. But are there any "gotchas" you know of in doing so? I guess the biggest "anti-gotcha" would be getting in trouble by duplicating Parent tables from other tables to avoid having to request a REFERENCES grant from the schema owner. The whole point, after all, with databases (at least a significant one) is to reduce or eliminate data duplication (except for controlled redundancy for a good, wellunderstood,  specific purpose).

Carl, you wrote:

> If your table is supposed to have a (key-wise) subset of another table and the key information is "sensitive" in
> the "parent table" then this feature supports the situation.

This makes sense to me too. Unless there's a subtlety I've missed-- again, quite possible.

Thanks again guys. I appreciate the replies.

Dana Received on Thu Jul 16 2009 - 06:52:39 CDT