Foreign Key Question 2

[bnhcomputing]

Table 1: Employee has employeeID and other info.

Table 2: Jobs has jobid, jobtitle and other info.

Table 3: EmplxrfJob Has employeeID and jobid only.

I should be able to set a foreign key in all tables so that:

Emplyee Table
Employee added, OK.
Employee deleted, cascade delete.

EmplxrfJob Table
EmployeeID must be in employee table.
JodID must be in jobs table.

I have tried every combination I can think of, nothing works. Either it won't let me set it up, or it won't let me add records.

Thanks in advance for any help.

Hubert Hoffman

 

[sleipnir214]

What version of MySQL?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[bnhcomputing]

Sorry for the lack of information.

I am using:

version 4.0.16-nt on a win2000 server
innoDB is my table type.

Hubert Hoffman

 

[sleipnir214]

In the situation where it does allow you to set up the foreign key constraints, how are you performing the inserts?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[bnhcomputing]

If I set the foreign keys, then I can not insert into any of the three tables.

Without the foreign keys, then I can add records.

EmployeeID 1, JobId 1, then I add a record to the Xref table 1,1.

This works OK, but then there aren't any constraints.

If I add the constraints before adding any records, then I can't add records.

If I add the constraints after I insert the one record, then I can't add any more, or delete any.

Hubert Hoffman

 

[bnhcomputing]

CREATE TABLE `employee` (
`EmployeeID` varchar(10) NOT NULL default '',
`FirstName` tinytext NOT NULL,
`LastName` tinytext NOT NULL,
`EStatus` char(1) NOT NULL default '',
`HireDate` date NOT NULL default '0000-00-00',
`TermDate` date default NULL,
`ReviewDate` date default NULL,
PRIMARY KEY (`EmployeeID`),
UNIQUE KEY `EmployeeID` (`EmployeeID`),
KEY `FirstName` (`FirstName`(255)),
KEY `LastName` (`LastName`(255)),
KEY `EStatus` (`EStatus`),
CONSTRAINT `0_294` FOREIGN KEY (`EmployeeID`) REFERENCES `employee_xrf_job` (`EmployeeID`) ON DELETE CASCADE ON UPDATE NO ACTION
) TYPE=InnoDB CHECKSUM=1

Once I add the constraint, I can't add new records to employee.

Cannot add or update a child row: a foreign key constraint fails.

Again, any suggestion is most appreciated.

Hubert Hoffman

 

[sleipnir214]

Your constraint is pointing the wrong way, isn't it? Don't you want values in Emplxrfjob to be constrained by values in Employee, not the other way around?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[bnhcomputing]

I want it both ways, but lets just start out with the employee table.

The way I read this is:

If I delete a record from employee, then delete all records with the same employeeID from the Xref table. If I add records to employee, do nothing. Am I misreading?

Once this works, then I will also add a constraint to the Xref table so that records can only be added if the employeeID is already there, BUT deleting the record from the Xref table would be OK.

 

[sleipnir214]

Both of the constraints you are talking about are constraints that apply to the xref table. Neither requirement should constrain the data entered into or the behavior of the employee table.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[bnhcomputing]

If I remove the constraint from the employee table, then it is possible to remove records from the employee table without removing the records from the Xref table leaving alienated records in the Xref table.

It was my understanding that the constraint with the cascade delete would prevent this. I have removed the constraint from the employee table, and verified that a constraint only on the XRef table does infact cause an alienated record problem.

Hubert Hoffman

 

[sleipnir214]

Again, you are not constraining the behavior of the employee table. You need to constrain the behavior of the xref table.

You want the xref table to exhibit the following behaviors:[ul][li]it cannot have an employee id or a job id that does not exist in the employee and job tables, respectively[/li][li]if the xref table cannot comply with the above constraint, it must delete the offending record[/li][/ul]

If the above is correct, the only table that should have any constraints is the xref table.

But you don't have to take my word for it. Try the following:

create table employee
(
   empid int unsigned auto_increment primary key,
   name varchar(50)
) type = InnoDB;

create table job
(
   jobid int unsigned auto_increment primary key,
   job_desc varchar(100)
) type = InnoDB;

create table emp2job
(
   e int unsigned,
   j int unsigned,
   index (e),
   index (j),
   constraint foreign key (e) references employee(empid) on delete cascade,
   constraint foreign key (j) references job(jobid) on delete cascade
) type = InnoDB;
  
  
insert into employee(name) values ('Fred Flintstone'), ('Barney Rubble');
insert into job(job_desc) values ('Clean up the quarry'), ('Clean up the office');

insert into emp2job values (1,1), (2,2);
select name, job_desc from employee, emp2job, job where employee.empid = emp2job.e and emp2job.j = job.jobid;

Then do:

delete from employee where empid = 1;

select name, job_desc from employee, emp2job, job where employee.empid = emp2job.e and emp2job.j = job.jobid;

And see what is returned.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[bnhcomputing]

PERFECT!!! Thank you very much. Only have constraints on the Xref tables and when I delete from a main table, the Xref tables are all cleared out automatically.

Thanks again for helping me understand how to read.

Hubert Hoffman