Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made approximately about 15 people in different times. And it was normal practice to us to create foreign keys on the reference tables in different schemas because it is really convinient and keeps database very clean, because we don't need to keep the same refernce tables in a different schemas.

But anyway my DBA (who created new instance with DB and copy my application inside of the Solaris zone) said very harsh today, "Foreign keys on the different schemas is evil and you need to destroy it!". He didn't explain his point of view.

Is it really bad idea to do that with a big applications?

share|improve this question
Your DBA should be sacked. – Colin 't Hart Dec 10 '13 at 12:31
We're all going to scream that your DBA is a moron if that's all they said, but are you sure there wasn't other context to your DBA's argument? – Kermit Dec 10 '13 at 14:04
maybe the DBA is just doing the best he can to support the ridiculous job the devs did in constructing this thing. – swasheck Dec 10 '13 at 15:28
@swasheck On the other hand, do you want to have his job after the database has accumulated several years of inconsistencies under this DBA? – Twinkles Dec 10 '13 at 15:39
@Twinkles not at all – swasheck Dec 10 '13 at 15:41
up vote 5 down vote accepted

Bearwith me - I come from SQL Server and hate oracle, but I Think the argumentation still stands.

Schema are nice to isolate tables from logical subsystems. Foreign keys guarantee data integrity. These are orthogonal concepts - as obviously data integrity between subsystems is also a must have. Accounting and Shipping and possibly Central CUstomer Data do not live in silos where a customer can get deleted while being used in accounting.

As such, I think the requirement of the DBA is a sign of incompetence. Please anyone can step in and provide a counter-argument - I would be happy. But this is how I do it on SQL Server (although, again, our definition of schema is IIRC a LITTLE different from the oracle definition).

share|improve this answer

While demanding the destruction of foreign key constraints without detailed reasoning is foolish, it makes sense to keep the outside references under control. What if the schemas you are referencing are named differently on your new server?

In Oracle you resolve this problem by creating SYNONYMS for objects that are outside of the current schema.

share|improve this answer
You can overuse synonyms and further muddy the question of "what does this refer to?". See here for more details on security, performance and best practices – kevinsky Dec 10 '13 at 14:58
Synonyms can't be used as the target of foreign key constraints. – Colin 't Hart Dec 10 '13 at 15:16
Valid points. And further proof that making statements without giving others an opportunity to argue the merits and dangers is bad. – Twinkles Dec 10 '13 at 15:33

The only "bad idea" that I can imagine from doing this, is that you cannot grant the REFERENCES object privilege (the one needed to create a constraint that refers to a table) to a role. I has to be done schema/user by schema/user.

Besides that, I dont see the point of your DBA.

share|improve this answer

Just think of of this: The child table owner schema begins to create record in its table and unknowingly prevents the parent table schema user from deleting records from the parent table. Is it something it anticipates and appreciates?

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.