Avatar of Cyber-Storm
Cyber-Storm
 asked on

A Question on Foreign Key Constraints

Hi there,

Lets say I have 3 tables, Employees, Rates and Work
Employees has a PK on the Employee Code.
Rates has a Foreign Key to the Employees table for the Employee Code. It's PK is on the Employee Code and the Rate (since there are multiple rates per employee).

Finally the third table holds all the Work the Employees do as well as the Rate they're doing it at.
Should this table have a Foreign Key to both the Employee and Rates table or only the Rates table since it already has a FK to the Employee table?

Could you also please go into a little more detail as to why you say either yes or no (a simple link to a page explaining what is best would be fine too).

I'm just not sure if you're overdoing it if you're adding 2 FK's or if that's the right way to ensure your data integrity.

Many Thanks,

Storm
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Cyber-Storm

8/22/2022 - Mon
dwkor

The problem is that your schema allows anomalies. Regardless how many FK do you have, even if physical data integrity would be correct, logical data integrity could be broken - think about the situation if Rates.EmployeeCode has been updated

I personally would change design of the system to something like that:

create table dbo.Employee
(
	EmployeeCode int not null primary key
)
go

create table dbo.Rates
(
	RateId int not null primary key,
	Rate money not null
)

create table dbo.EmployeeRates
(
	EmployeeCode int not null,
	RateId int not null, 
	
	constraint PK_EmployeeRates
	primary key clustered(EmployeeCode, RateId),
	
	constraint FK_EmployeeRates_Employee
	foreign key(EmployeeCode)
	references dbo.Employee(EmployeeCode),
	
	constraint FK_EmployeeRates_Rates
	foreign key(RateId)
	references dbo.Rates(RateId)
)
go

create unique nonclustered index IDX_EmployeeRates_RateId_EmployeeCode
on dbo.EmployeeRates(RateId, EmployeeCode)
go

create table dbo.Work
(
	WorkId int not null primary key,
	EmployeeCode int not null,
	RateId int not null, 
	constraint FK_Work_Employee
	foreign key(EmployeeCode)
	references dbo.Employee(EmployeeCode),
	
	constraint FK_Work_Rates
	foreign key(RateId)
	references dbo.Rates(RateId)	
)
go

create index IDX_Work_EmployeeCode on dbo.Work(EmployeeCode)
create index IDX_Work_RateId on dbo.Work(RateId)
go

Open in new window

Cyber-Storm

ASKER
So would the EmployeeRates Table only be used when you're looking for an Employee at a given Rate?  I ask this since I don't see any linkage between the Work Table and the EmployeeRates Table.

In my scenario above, an After Update Trigger on the EmployeeRate.EmpCode field would prevent any Updates (or if you do need to update it, then simply use an Update Cascade) to ensure logical integrity right?

In doing further research last night on my question, I read up a bit more on Cascading, and there it said that you must only ever have 1 way to reach a table in the chain, that would mean, that I should then only have 1 FK going from the Work Table to the EmployeeRate table since that is the most logical Contraint and then drop the FK going to the Employee Table.  Did I understand this correctly? Here is a link to the page I read about it:
http://msdn.microsoft.com/en-us/library/aa933119(SQL.80).aspx
ASKER CERTIFIED SOLUTION
dwkor

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Cyber-Storm

ASKER
Thanks very much, after much reading, I think I'm understanding things a lot better now.

I guess what I'll most take away from this is "Just because you can create a FK, doesn't mean you should".  Valuable insight, thanks!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes