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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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!