14

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?

Improve this question
8
  • 13
    Your DBA should be sacked.
    – Colin 't Hart
    Dec 10, 2013 at 12:31
  • 3
    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, 2013 at 14:04
  • 1
    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, 2013 at 15:28
  • 2
    @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, 2013 at 15:39
  • @Twinkles not at all
    – swasheck
    Dec 10, 2013 at 15:41

5 Answers 5

Reset to default
5

Schemas 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.

This is how I do it on SQL Server (although, again, our definition of schema is IIRC a LITTLE different from the Oracle definition).

As such, I think the requirement of the DBA is a sign of incompetence. T

Improve this answer
4

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.

Improve this answer
3
  • 1
    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 stackoverflow.com/a/10042117/851930
    – kevinskio
    Dec 10, 2013 at 14:58
  • 4
    Synonyms can't be used as the target of foreign key constraints.
    – Colin 't Hart
    Dec 10, 2013 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, 2013 at 15:33
2

If you have the need (space/security/whatever) to move any schema to a different database, you won't be able to handle the references any longer. That is probably the main reason to request killing the references.

Improve this answer
1

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.

Improve this answer
0

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?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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