Re: Experience with foreign keys across schemas?

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:73e20c6c.0411200202.6dc9091f_at_posting.google.com...
| Rick Denoire <100.17706_at_germanynet.de> wrote in message
news:<re1qp0hbc4vads0k1b4p2qr5c5cqfbq29g_at_4ax.com>...
|
| > Today, I was asked to setup a new schema with tables which will
| > contain foreign key references to tables in another schema.
|
| Nothing wrong with that. Works like a charm.
|
|
| > I don't like that. I am sure that's wrong, but am missing arguments.
| > OK, things like "delete from table <tralala> cascade" or "drop user
| > <john> cascade" tend to be dangerous. What else?
|
| What's that got to do with multiple schemas and RI, boss?

Can't find the OP text so, I'll jump in here

Rick,

This is really very, very normal and totally supported by Oracle.

There is no DELETE...CASCADE command, but when you define an FK constraint, you can specify ON DELETE CASCADE, if appropriate. If not appropriate, do not specify it. Note that it's more 'dangerous' to not have the FK constraint, thus allowing bad (unvalidated) data values.

Regarding drop user xxx cascade --- read the docs on that one. The cascade drops that user's objects, and FK constraints that refer to those objects. However, no data values for those other schema's FK constraints are lost (just the reference row in the dropped PK table). Plus, the only way those other schema's FK constraints could exist in the first place is because REFERENCE privilege was explicitly granted by the owner of the referenced tables. So, the table owner remains in complete control from start to finish, but does not directly control any data in the referencing tables (the referencing table's owner has complete control over the FK definition, as long as the REFERENCE privilege and the referenced table exist)

++ mcs Received on Sat Nov 20 2004 - 07:17:06 CST