February 13, 2014 at 10:05 pm
JeeTee (2/12/2014)
Hi SSC,...
Yesterday, a senior database developer colleague of mine described the use of foreign keys as "purely academic" citing that FK's provide little benefit that can't be enforced through procedural or application logic, thus freeing up database developers to affect code changes more quickly.
...
If you really feel the need to refute his point, you will probably be able to locate numerous examples of orphaned and/or invalid data in columns that should be foreign keys in under an hour. Let the system that he is supposedly designing and maintaining tell him that he is wrong. 😎
As others have stated, it is virtually certain that there are problems with data integrity that foreign keys would have prevented.
There are plenty of clueless people that love to say stupid things about databases.
Examples of things I have been told more than once:
1. Primary keys are unnecessary.
2. Foreign keys are unnecessary and just get in the way.
3. The database is "overnormalized".
February 14, 2014 at 6:55 am
Let the system that he is supposedly designing and maintaining tell him that he is wrong.
I've seen otherwise good people unconvinced by even this.
February 14, 2014 at 12:51 pm
There are cases where FKs are hard to implement and there is a need to do it in the application, especially when you are doing in-table audit trail. Here's a case where I have a table:
create table Tbl
(
IndexID int identity(1,1),
ID int,
Value int,
BeginTime datetime default getdate(),
EndTime datetime default '1/1/2100',
primary key pk_Tbl ( IndexID )
)
create index ix_Tbl_ID on Tbl ( ID )
create index ix_Tbl_EndTime on Tbl ( EndTime )
The application PK is really column ID, not the identity column IndexID. Adding two values to Tbl:
insert Tbl ( ID, Value ) values ( 1, 10 ), ( 2, 20 )
Table updates are always done with inserts and updates of EndTime only. Let's say I want to update Value to 100 for ID = 1. That is:
begin transaction
update Tbl set EndTime = getdate() where ID = 1 and EndTime > getdate()
insert Tbl ( ID, Value ) values ( 1, 100 )
commit transaction
The result is:
IndexID ID Value BeginTime EndTime
1 1 10 [old_time] [new_time]
2 2 20 [old_time] '1/1/2100'
3 1 100 [new_time] '1/1/2100'
My queries to get the most recent data is based on EndTime > getdate() for any given ID. In this case, ID is not unique, so another table cannot create FK to ID in Tbl.
Sure I can create a unique constraint with ID, EndTime and BeginTime, but then I need to pass around the primary table EndTime and BeginTime to all child tables, which is not a viable option.
February 14, 2014 at 1:00 pm
There may be times FKs are hard to implement, and there may be some that don't have FKs at all. FKs enforce relationships. Where there is no relationshipo there is no need for an FK. But that on some tables it would be difficult or inappropriate doesn't mean FKs aren't important. No one is advocating to find a way to use them no matter what it takes. Most on this post are saying they are essential and not academic.
February 14, 2014 at 1:09 pm
I agree they are not academic, but it depends greatly on the applications and database designs. I am so used to not being able to use FKs that I don't use them at all, even when I can. Without knowing JeeTee applications, it is quite possible that's what the senior person tried to say.
February 14, 2014 at 1:17 pm
Michael Valentine Jones (2/13/2014)
3. The database is "overnormalized".
That can be true (even if it usually isn't).
February 14, 2014 at 1:17 pm
I don't use them at all, even when I can
Well there's no point in taking time trying to convince you. As I said to another poster, one day you or someone else will clean the the messes that will inevitably result. I have cleaned others messes before, and I expect to do so again.
February 14, 2014 at 1:30 pm
I've cleaned up before and I sure will cleanup again, no doubt. Maybe I'm lucky and it only happened a handful of times over the last 15 years or so. Again, I'm not advocating one way over another, but posting my own experience. You also quoted me out of context. The entire quote should be I am so used to not being able to use FKs that I don't use them at all, even when I can. 😉
February 14, 2014 at 2:58 pm
Luis Cazares (2/14/2014)
Michael Valentine Jones (2/13/2014)
3. The database is "overnormalized".That can be true (even if it usually isn't).
I am not sure what "overnormalized" even means, but anytime someone said that to me, they didn't understand what "normalized" even means.
February 14, 2014 at 3:08 pm
It's more for databases meant for reading and not for writing. Basically reporting or some kind of OLAP.
February 14, 2014 at 4:58 pm
RonKyle (2/14/2014)
Let the system that he is supposedly designing and maintaining tell him that he is wrong.
I've seen otherwise good people unconvinced by even this.
There aren't many people that are glad to hear proof that they are wrong. Especially if they're used to slinging their BS unchallenged.
However, it's important to convince other people, so objective evidence is a powerful tool for that.
February 15, 2014 at 3:40 am
Michael Valentine Jones (2/14/2014)
Luis Cazares (2/14/2014)
Michael Valentine Jones (2/13/2014)
3. The database is "overnormalized".That can be true (even if it usually isn't).
I am not sure what "overnormalized" even means, but anytime someone said that to me, they didn't understand what "normalized" even means.
It's usually a case of improperly normalized rather than over normalized.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply