foreign keys(urgent )

[wwalice]

Hello, every body:

I have a urgent situation. Now we are setting up the production database. I have 50 tables in the schema. Now somebody suggest adding primary keys and foreign keys to almost every table. That mean there will be about 40 primary keys and 40 foreign keys. I also created indexes on most of the tables based on queries.

I do not think we should add all the foreign keys. We are OLPT system. I think the foreign keys can help us a little and they are expensive. For example, the foreign key on Customer.customerId just make sure whenever the user add the entries to other tables his cutomerId should be found in customer table. We use codes to add entries, get information.

I think the disadvantage with the foreign key is more than
advantage. How about it?

Could you tell me your consideration?

We must make decision by today' noon.

Thanks.

[Jang]

Well, in my opinion the real issue is one of data integrity.
If the application is adequately handling the data integrity and enforcing the relationships, then you are correct, the database doesn't need to do it also.
However, if the application is not, the database should.
This may be an oversimplification for your case, but as stated above, the real issue is ensuring the data that is stored is valid.

[omegamark]

Fks are used for Maintaining the integrity of the Business rules.So you should apply them in places whereever it is needed.

You are telling that you have only 50 tables .That 's not a big deal for oracle...

[jdjoshi]

FKS are always good especially if u have a network based environment. By letting Oracle tackle these data integrity concerns, you are achieving the following :

1) Thin client as u don't have to worry about all these validations
2) Reducing network contention as u don't have to go back and forth to the DB for validations
3) Oracle will do validation faster than any other application code so it will be FASTER.

Happy coding !!!

[wwalice]

Thanks for immediate response.

But when you use fks you need maintain indexes. Every time when you delete, insert you also work on the index. Especially when you import the data the fks will bring a lot of additional work.

Does somebody have a real experience on it? Sure, Oracle can handle it. But that is not what i concern.