3

I am designing a oracle database for Timesheet application. I am wondering if it is really necessary to have the foreign key constraints on tables (Master and Child).

As we know that theoretically it is good to have a proper referential integrity on tables but do we really need them?

I heard that foreign keys make the database work extra on every DML operation because it has to check FK consistency. This can reduce the performance. But on the other side it can be helpful in the situation where the primary key has been deleted before inserting a new row in the child table.

Performance is a main issue in the timesheet application and there will be about 250 people filling up their timesheets at the end of the month (possibly at the same time).

If i don't have a foreign key constraint on table then do i have to check it first (in stored procedure) if the primary key exists in the master table before inserting a new record in the child table each time?

Added: In the past, i have worked with many experience oracle database developer and we never used the foreign key constraints on tables.

user1263981
  • 2,953
  • 8
  • 57
  • 98
  • 9
    250 users is not a lot. Especially if it is only once a week - you shouldn't be concerned about any perceived performance hit from foriegn key constraint checking. – slugster Jul 02 '12 at 12:23
  • 1
    http://stackoverflow.com/questions/2317391/is-there-a-severe-performance-hit-for-using-foreign-keys-in-sql-server – Habib Jul 02 '12 at 12:25
  • 3
    Everything that you don't check in your database will eventually contain invalid data. So unless you want to have a big pile of data junk in a few years time, implement your constraints. – Rene Jul 02 '12 at 12:46
  • 4
    You have *never* worked with experienced Oracle developers. You have been working with cowboys who don't understand the workings of databases in general and Oracle in particular. – APC Jul 02 '12 at 13:52

5 Answers5

14

I'd say develop the application with foreign key constraints in place, then measure the performance, and if performance is an issue, measure the difference between the DB with FK constraints and without, and if they prove to be a performance issue, consider eliminating them.

It is unlikely that you'll find that they are the source of any performance issues, and I wouldn't recommend omitting them entirely from the outset on the guess that it will lead to better performance.

You don't need them, in the same way that you don't need to validate input, wear safety belts, etc.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 8
    +1 plus: just because of a tiny bit of performance, I would **NEVER EVER** sacrifice my referential integrity - **especially** not in production! – marc_s Jul 02 '12 at 12:30
  • If i don't have a foreign key constraint on table then do i have to check it first (in stored procedure) if the primary key exists in the master table before inserting a new record in the child table each time? Wouldn't it slow down the DML operations becuase i will be quering master (for PK existance) table everytime i insert a new record ? – user1263981 Jul 02 '12 at 12:38
  • By running a query like you said (pre every insert or update & delete), you're doing some form of validation for that key field, thus enforcing referential integrity of the keys. As to performance, i think your best bet... run a test and see whats what? :/ – Rohan Büchner Jul 02 '12 at 12:48
  • 3
    @user1263981 - Running a query against the master table before every insert to the child table will almost certainly have *poorer* performance than implementing a foreign key, since the results will have to be returned to the application - and to be safe, a transaction would have to be held open for that call and the insert. Obviously, if you don't know which master row a child row belongs to, you'll have to query the master table to identify it, but that's true with FKs or without. – Damien_The_Unbeliever Jul 02 '12 at 12:57
4

With referential integrity, there is a slight performance hit when key columns are inserted or updated. However, this is almost always more than compensated for by

1) Reliable and consistent data

2) Significantly improved query times

Really, though, if you're not enforcing referential integrity, why on earth would you use a referential database?

Barracoder
  • 3,696
  • 2
  • 28
  • 31
3

Any method of ensuring that the foreign key is enforced (for instance in sp or trigger), will be at least as slow as the referential constraint (expect it to be much slower). The only possibility that is faster is to make sure that your application has no bugs whatsoever and that it does not create lost references. This is too difficult for some reasons:

Consider the following scenario: Table A has a Column that refers to Table B, one user inserts a row in B, and before anyone uses it in A, decides to delete it... Meanwhile, Another user has already opened the insertion form for A which has a combobox filled with rows of B. When he saves this A, it can refer to a wrong B, and I see no natural architecture to prevent this, that can be possibly faster that the relationship itself.

I can go on with as much examples as you wish, but in short, my experience in application architecture says keep as much schema-level constraints as possible and have more time to spend with family!

Alireza
  • 5,421
  • 5
  • 34
  • 67
3

In addition to what others have said regarding data consistency and query performance...

Having the foreign keys in the database is also documentation-- you don't need to rummage through your code base to understand how the tables should relate. This becomes more important as the number of tables in a database increases or the number of applications hitting that data grows (or you don't have access to the code and you still need to make sense of the data).

gsiems
  • 3,500
  • 1
  • 22
  • 24
1

My 2 cents... 250 people is nothing to be worried about. Databases can handle vast amounts of data and references. That's their purpose. Obviously use your head when going about creating keys and structures. But you'll have accurate data with the keys in place, as opposed to a possible nightmare come time to build reports and what not... And at the end of the day... that's the point of an application like you described, ease and assist data input and enforce data accuracy.

Rohan Büchner
  • 5,333
  • 4
  • 62
  • 106