1

I was a developer in a certain project developed under sql-server and .Net, they don't use physical relations between their tables but they use logical ones " logical foreign keys ".

I asked them that for what reason they do that ,they say "it is more optimal".

What I really want to know, is it really more optimal or it is just a myth?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Sleiman Jneidi
  • 22,907
  • 14
  • 56
  • 77

3 Answers3

2

When it comes to reads from a database, whether foreign keys are defined or not doesn't come into it. There is no relationship between having foreign keys and the performance of reads.

Things that will effect performance are how the tables are stored, what indexes are defined on them and the stored statistics (just to name a few).

This is a bad justification for not having referential integrity in the database (in particular as it can be trivial to test).

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • 2
    [In some cases in fact the presence of a trusted FK constraint can reduce the number of reads.](http://blogs.msdn.com/b/conor_cunningham_msft/archive/2009/11/12/conor-vs-foreign-key-join-elimination.aspx). i.e. having FKs can be a benefit. – Martin Smith Dec 20 '11 at 23:22
2

Using the assumption that " logical foreign keys " are just values that reference a key in another table without a physical link between them in terms of constraints I can tell you what the benefits of the physical link is.

First of all a "physical" foreign key is a constraint and it enforces referential integrity between the two values. So that, if you want for example to use a foreign key that doesn't exist in the other table you will receive an error. The same thing will also happen if you try to delete a key that is a foreign key by constraint in another table.

Secondly it is arguable that it is more optimal since you can index the foreign key constraints and benefit from that for example when you use joins.

More on this: http://msdn.microsoft.com/en-us/library/ff647793.aspx

TheBoyan
  • 6,802
  • 3
  • 45
  • 61
1

There is actually no physical difference between a "real" foreign key and a "logical" foreign key. They're both just columns in a table and don't affect the way that a table is stored on disk. This actually surprised me too when I first learned.

The only difference is that when you have a "real" foreign key, whenever a delete, update, or insert statement is ran on a table, the database server has to check that the value is being updated to a legitimate value. If you look at the execution plan for a statement that's an update, insert, delete, or merge, you'll actually see it has to scan or seek on all tables that have a foreign key.

This can be quite a performance overhead if there are a lot of foreign keys or there aren't helpful indexes.

Picture you have a table for Companies, and then another table for Employees. Your employees table will likely have a column called companyId.

When you run:

delete from Companies where companyId = 123;

The database server needs to make sure that there aren't any employees for that companyId. The same applies when you run:

 insert into Employees (companyId, name) values (123, 'John');

The database server needs to search the companies table to make sure that the companyId 123 exists.

Yes it is faster to have only "logical" foreign keys. However, it comes at the cost of possible data corruption and might cost more time finding bugs and other sources of data corruption. Whether it's worth it is up to you. One thing to consider is that it doesn't affect read-only queries.

Edit As Martin Smith pointed out and I had left out, there are some cases where the foreign key would be faster. If there is an inner join on a table with a foreign key, and no columns are referenced by the second table, then the query doesn't have to hit the second table since it can trust the foreign key.

John Gibb
  • 10,603
  • 2
  • 37
  • 48