Urgent - No FK at all!!

[farrokhp]

I just noticed that our production database schema doesn't have "any" Foreign Key constraints at all and we got about 235 tables and they are pretty related!! The orginal . application developed by a third party company and I guess for the sake of convienece they didn't bother with
Foreign Key and just develope it fast and run away with money!!! And inside the application thay take care of this.
Looks like it is not a show stopper but My question is does it crucial that we don't have any FK and what we loose in general and what is the biggest gain we get if we put lots of effort and review the database and add all FK and is it too late or not? Please elaborate.

[sambavan]

Trying to add Fk on an live production system could easily cause a havok. So my first suggestion would be not to bother much if the application is functioning fine. By not using the Fk, you are failing to enforce the relational intergrity. But if the application takes care of it, then just don't bother. By not using a FK, any one who has direct access to the database tables can easily cause a havoc on a production system, by either inserting any non relevent data into it.

If your superiors agree, then set up a team to study the application throughly and then start deriving the ER and enforce the foreign key. One of the best tools that I could suggest off of my head would be designer. Do a table retrofit and look into the schema and make the modifications appropriately. Some times it could be to avoid any circular relationships too.

Sam

[KenEwald]

Is the application checking for key constraints?

If you enabled a key constraint on your database, would the application know how to handle the errors?

-Ken

[pando]

some application dont have FKs (like Oracle Financials, I think SAP as well), I think it's because the access would be faster since there are no checks and of course you can only access to the application from front-end, may be this is your application case

[akwete]

hi farrokhp,

My expertise is on Database Design, modeling, Development and Performance. If you need help with the establishing relational integrity constraints in your database, you can contact me directly at ckk@att.net.

Regards.

[Mr.Hanky]

I would listen to Sambavan on this one, if it ain't broke don't fix it, especially a production box. Most of the DB's in my company do not use RI (referntial integrity) so it is nothing to worry about.

You have what we call a "black box" third party designed and now you have to support it, hard to look into isn't it?

MH

[sambavan]

Also remember, if you were to retrofit and enforce the referential intergrity constraints on it, you are also liable to be sued by the vendor, as you might be violating the licence agreements. Before you do anything, read your licence agreement with your company lawer and proceed!


Sam

[akwete]

Well, I will sort of agree with Sambavan for you to read all the license issues. But I am working on a similar project now where the web database was developed without any relationship between the tables. This made the database to run very slow and without much data integrity. I have first of all reorganized the database table structure redefining the ERD and establishing the integrity constraints. Also I moved the database from SQL Server to Oracle 9i. Right now the e-commerce database is working very well and fast.
Therefore, I disagree with Mr. Hanky on his opinion.
You definitely need relationships between your database tables.

Cekeke
Apps Dev/DBA

[Shestakov]

Originally posted by cekeke

Therefore, I disagree with Mr. Hanky on his opinion.
You definitely need relationships between your database tables.

Some applications build FK integrity using triggers, for example.
In some applications standart FK integrity not enougth, because its should stay alive chilld rows (and set FK fields to some default value) when we delete parent rows.
Some applications should works not only on Oracle DB.

Very many reasons when application don't use FK.

[farrokhp]

Thanks Sam for your good explanation. I think I follow what you said. I think what you mean by Havoc is if we enable FK in Live PROD now and if there is no exception handler in Stored Proc the App blow out. Also does Erwin can be helpful like Designer or not? Regards.