Removing foreign key constraints, referential integrity, and hibernation

Question

My colleague noted that our DBA client suggested removing all foreign key constraints in our Oracle DB schema. Initially, I did not agree with the decision. I am not a DBA developer. Therefore, later I realized that there could be some reasons for the decision. Therefore, I am trying to get the pros and cons of this decision.

Information about Proj:

• Spring app with persistent hibernate.
• Oracle 10g DB
• Batch jobs use only the SQL loader or plain JDBC.

Here is my list of pros and cons (Please correct me if I am wrong)

Pros:

• Because application management is supported by Hibernate, cascading a foreign key is optional. it is controlled by Hibernate with the appropriate cascading option.

• The Hibernate DELETE action (includes the cascade delete option) deletes the foreign key table entries before deleting its primary key entry (i.e. to avoid referential integrity issues). This behavior is the same for the case without a foreign key, the case with a foreign key and the case with a foreign key with a cascade. But adding a foreign key will unnecessarily slow down the Oracle uninstall operation.

against

• Hibernate provides a mechanism for managing the association between objects and cascading operations within an association. But it never provides the complete relational integrity solution that a database has.

• Referential integrity is required for batch jobs that use only the SQL loader or plain JDBC.

Guys, I need your advice. If any of you are a database administrator, provide reasons for the DBA.

Thank.

Answer 1

, .

"QC script" ( ).

, ( ) , . :

• .
• .
• NULL.
• .
• . , . , VIEW ?

, QC script , .

, , . .

Answer 2

! , , . .

( ): , .

Oracle, 20 . Oracle 6 10G 11g - . , ? Forms 3.0, ++, Forms 6i, Express. , - ; , , SOA-...

, Oracle ?

Answer 3

, , , .

, ,

CREATE VIEW orders_vw AS
SELECT ord.order_id, ord.customer_id, lin.product_id
FROM orders ord JOIN order_lines lin on ord.order_id = lin.order_id

, SELECT product_id FROM orders_vw WHERE order_id = :val , order_id order_lines , , , , . , order_lines , .

, .

Answer 4

Typically, deleting a foreign key is where the optimization of database performance begins. This is a kind of compromise: you sell guaranteed integrity at the DBMS level and you must manage it yourself (which is quite easy with Hibernate, but requires very accurate information in simple SQL), and you get increased query performance, since external key checks in queries are quite expensive.