MySQL Forums
Forum List  »  Newbie

Data loading with foreign key contraint - help please
Posted by: geekhunter_nospam
Date: August 29, 2004 01:54AM

Sorry for reposting, but my subject of the post didn't make much sense the first time around.

I'm a total newbie to mySQL, but was hoping someone could answer a question regarding adding a record into a database that has foreign key constraints. Remember, I'm a total newbie so I'm hoping I'm using the right words to express this. I'm taking a class that required us to use an ER diagramming tool. This tool generates SQL table create scripts in mySQL. After a little tweaking I got the scripts to work. An example is as follows:

# Create Table : 'Jobdesc' Job Description for Requisition
# desccode :
# jobdescription :
# levelcode : (references JobCode.levelcode)
# jobcode1 : (references JobCode.jobcode)
# jobcode :
# titlecode : (references JobCode.titlecode)
#
CREATE TABLE Jobdesc (
desccode CHAR(8) NOT NULL UNIQUE,
jobdescription MEDIUMTEXT NOT NULL,
levelcode CHAR(2) NOT NULL,
jobcode1 CHAR(8) NOT NULL,
jobcode CHAR(8) NOT NULL,
titlecode CHAR(7) NOT NULL,
PRIMARY KEY (desccode,jobcode),
INDEX idx_fk_Jobdesc (titlecode,jobcode1,levelcode),
CONSTRAINT fk_Jobdesc FOREIGN KEY (titlecode,jobcode1,levelcode)
REFERENCES JobCode (titlecode,jobcode,levelcode)
ON DELETE CASCADE
ON UPDATE CASCADE) TYPE=INNODB;

This is a create script for a job description table. Job descriptions are related to a jobcode table. That table in turn is related to joblevel and jobtitle tables (i.e., the job title and job level determine the job code). The jobcode is needed for each job description.

One problem I have is that the create scripts generated from the ER tool makes all fields in the job description entity NOT NULL. If I try to just enter the description code (desccode), the job description (jobdescription) and the associated job code (jobcode) I get the following error:

#1216 - Cannot add or update a child row: a foreign key constraint fails

This happens if I just try to enter the desccode, the jobdescription, and jobcode datta. I think this is happening because jobcode1, levelcode, and titlecode are NOT NULL so when I update the record it attempts to enter data (NULL) into these fields which are child row. Can someone explain what I should do? Should I just change these fields of data into NULL?

Any help would be appreciated.

Todd

Options: ReplyQuote


Subject
Written By
Posted
Data loading with foreign key contraint - help please
August 29, 2004 01:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.