foreign key constraints

[zoey]

Hi,

Can someone tell me whether or not, generally, that NOT having foreign key constraints is a good idea or not?

We first had a database that did have foreign key constraints and now we don't...since the fkeys were removed, the database works better.

I know it depends on the database and the data, etc. But, is there a general perception in the dba world whether or not foreign keys should at least try to be used in order to maintain data integrity?

Zoey

[pando]

If you dont have foreign keys how are you relating your tables...? How do you do joins? What happen if one table depends on another but there is no foreign key to enforce the business rule? You said your database is working faster so I guess te foreign keys you removed were redundant since it seems like it's working faster (and actually working!)

If your database is datawarehouse you can have no constraint at all since all data which are to be loaded to the database are supposed to be from a database with all required constraints therefore the data integrity is there.

[chrisrlong]

In an OLTP database, it is ultimately the job of the database to maintain RI. Therefore, you will be hard pressed, IMHO, to find many database designers who would argue against Foreign Key constraints. It is generally considered a necessity.

However, having *indexes* on all those FKs is another story. It is not entirely uncommon to have tables that have a few dozen little FKs to code tables. Indexing all of these FKs can truly hurt DML (Insert, update and delete) performance. *However*, the flip side of this coin is that there is a big gotcha to watch out for. If you plan on deleting records from a parent table, or modifying the key of a parent table, then you had better put indexes on all the FKs in all the child tables that point to said parent table. Here is the link explaining why in detail:
[url]http://oradoc.photo.net/ora8doc/DOC/server803/A54642_01/ch6.htm#1574[/url]

Basically, and this is 8x-specific, when you delete a record from a table, it will must go out to all the child tables of the table from which you are deleting. It goes out to all these child tables to verify that they do not have any records that point to the one you are deleting. It will do this harmlessly through an index if one exists. IF NOT, then it will lock the entire child table, and throw a major monkey wrench into the concurrency of your database.

*This* would be my best guess as to what happened to you. You probably had some un-indexed FKs that really should have been indexed. Removing the FKs removed the problem. Unfortunately, it also removed all your RI. A far better solution would be to actually index those columns that fit this profile.

The other possiblity is that you indexed *all* your FKs. This, as I explained above, can also have major performance penalties. In this case, you should probably only index those columns that need to be indexed, ie., those that are needed to avoid the issue described above and those truly useful for queries.

Note, finally, that in order for an index to actually 'count' as a FK index, it cannot be a BITMAP index (taboo in OLTP anyway) and it must be the first column in the index.

Hope this helps,

- Chris

[chrisrlong]

BTW, the FK index issue also existed in 7.x, but with different characteristics than the ones I described. It was actually worse back then.

- Chris

[pando]

Hi Chris

I am having trouble indexing the foreign keys due to this locking issue.
The probem is we have a table (many queries do joins with this table) whcih has about 10 FKs and same columns (or composite columns) are repeating for each FK. For example col1 and col2 references col1 and col2 in another table then I would have another FK, col1, col2 and col3 referencing another table col 1, 2 and 3, then I would have another FK which is col1, col3 and col5 referencing col4 and 5 of another table... now I would like to ask how do I index this? Do I index col1 three times?? But with different combination? If I index all these foreign keys I am really worried that the DML would be so slow!

[chrisrlong]

Hmmmm...

First of all, this highlights the problems with multi-part PKs. IMHO, every table should have an abstracted, single-value numeric PK. This makes multiple things easier including the size and complexity of multi-parent children. If you had this setup, for instance, you would only need a single-field index for every FK, instead of the multiple field indexes you now require.

Unfortunately, you are already down this road, so what else can we do? First of all, the FK index is only a problem if you can delete from the parent (or change the PK value, but since you should never be changing PK values anyway, this shouldn't really be an issue). Hopefully, this would eliminate some of the tables from the equation. However, I'm sure you looked at that and still have an issue with at least some of your tables. So we need a bunch of indexes on, unfortunately, multi-part, overlapping keys. The only thing I can suggest is that you try to make some of them 'out of order'. I haven't tested this yet (I don't have multi-part PKs :) ), so please let me know if it works. For your provided example, you have FKs on:

- Col1, Col2
- Col1, Col2, Col3
- Col1, Col2, Col5

First of all, a single index on Col1, Col2, Col3 will cover both of the first 2 cases, since the first case is a left, leading subset of the second case.
As for the third case, can you possibly make an index on Col5, Col2, Col1 or Col5, Col1, Col2? The multi-part key *as a whole* matches the left-leading part of the index, so it might 'count' in Oracle's eyes, I'm not sure. Let me know if it works. The other advantage would be that if Col5 is also an FK (by itself) , then this index would cover that as well.

Good Luck, and let me know what you

- Chris

(Isn't this a nice little 'feature'? :) )