Foreign Key Constraint Error

My first question is why have you got 2 fields the same in any 1 table ?

The easiest way around your issue is to set an update trigger on the initial table

My first question is why have you got 2 fields the same in any 1 table ?"
I guess that this will be because they are being used to implement two different relationships. An example of this would be in a many-to-many relationship when the same table is being used at both ends of the associative entity.

e.g. in education a course of study can have pre-requisites, the relationship between a course and the list of pre-requisites is a many to many (a course can have many pre-requisites and a course can be a pre-requisite for many other courses)

Roughly designed as follows:

Course table
CourseID

PreRequisite table
CourseID - FK to course on CourseID (implements the relationship "course has many pre-requisites")
PreRequisiteID - FK to course on CourseID (implements the relationship "prerequisite course belongs to many courses")

I have marked this thread because I have a similar problem - in fact I have just described it above

My initial reaction is to stop using the same table twice and implement the relationships as tables.

Anyone else had this this sort of issue, and if so are there other options apart from using a trigger ?

cheers all

The reason is because one table "AT_APPLICANT" contains the two fields (cam & csr) that represent employees of our company who represent that person, but in different areas. For instance, the cam employee will handle account management between the applicant and clients, whereas the csr deals with relations with the applicant to get information during processing. Two different jobs, both are employees of our company. Our company employees are maintained in our Employee table. Therefore, the id field from the Employee table will point to two fields in the AT_APPLICANT table. Does that make sense?

I will take a look into what you said.

-Ovatvvon :-Q

Anyone?

-Ovatvvon :-Q

Love it or hate it, SQL Server won't let you do that.

The only way to implement such cascading is with triggers and no foreign keys (= don't do it unless REALLY necessary).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

FWIW here is the reference I found a few weeks ago :-


...and here is how I wound up implementing my relationships to get around the similar issue at my end (using your table names), fortunately I am allowed to alter my db structure whereas you may not have this luxury

Looks like its trigger time

CREATE TABLE AT_APPLICANT
(
APPLICANTUID INT,
PRIMARY KEY CLUSTERED (APPLICANTUID)
)

go

CREATE TABLE Employee
(
EmployeeUID INT,
PRIMARY KEY CLUSTERED (EmployeeUID)
)

go

CREATE TABLE CAM
(
EmployeeUID INT,
APPLICANTUID INT
)

CREATE TABLE CSR
(
EmployeeUID INT,
APPLICANTUID INT
)

ALTER TABLE CAM ADD CONSTRAINT fk_CAM_Employee FOREIGN KEY(EmployeeUID) REFERENCES Employee
(EmployeeUID)
ON UPDATE CASCADE

go

ALTER TABLE CAM ADD CONSTRAINT fk_CAM_Applicant FOREIGN KEY(APPLICANTUID) REFERENCES AT_APPLICANT
(APPLICANTUID)
ON UPDATE CASCADE

go


ALTER TABLE CSR ADD CONSTRAINT fk_CSR_Employee FOREIGN KEY(EmployeeUID) REFERENCES Employee
(EmployeeUID)
ON UPDATE CASCADE

go

ALTER TABLE CSR ADD CONSTRAINT fk_CSR_Applicant FOREIGN KEY(APPLICANTUID) REFERENCES AT_APPLICANT
(APPLICANTUID)
ON UPDATE CASCADE

go

insert into Employee values (1)
insert into Employee values (2)
insert into Employee values (3)

insert into AT_APPLICANT values (1)
insert into AT_APPLICANT values (2)
insert into AT_APPLICANT values (3)

/* Set up relationship between Employees and ApplicantsR. Employee 1 is CAM of applicant 1,
employee 2 is CSR of applicant 2, employee 3 is both CAM and CSR of applicant 3.
Fire off some test data and see what happens.
*/

insert into CAM values (1,1)
insert into CSR values (2,2)

insert into CAM values (3,3)
insert into CSR values (3,3)

select * from CAM
select * from CSR

update Employee set EmployeeUID = 10 where EmployeeUID = 1
update Employee set EmployeeUID = 20 where EmployeeUID = 2
update Employee set EmployeeUID = 30 where EmployeeUID = 3

select * from CAM
select * from CSR

update AT_APPLICANT set APPLICANTUID = 30 where APPLICANTUID = 3