Since I put in foreign keys with the same name as the primary key in the related table, the GUI tool had no choice but to create "new" foreign keys with the same name appended with the number 1. The end result... total chaos. Fixed it though. I really appreciate your help.
Todd
On Aug 30, 2004, at 6:20 AM, [EMAIL PROTECTED] wrote:
Foreign keys are used to enforce foreign relationships. Translated:
Certain data values must exist in one table before another table can
contain those values in columns that participate in foreign keys. Because
data must first exist in one table before it can be used as data in
another, you are required to fill in your FK-related structures from the
top down.
Start with your top-most table(s) in your structure (these are the ones
that the foreign keys are referencing but have no foreign keys of their
own). I think you said that you called them "joblevel" and "jobtitile".
Fill those tables with data. With those values in place you can create
rows in the jobcode table that re-use certain values. You will not be able
to assign a value to any row in jobcode that does not exist in either
joblevel or jobtitle (for the columns that reference those tables as
foreign keys).
Keep filling in values in each layer of your structure until you get to
the "bottommost" table(s). (These are the tables that FK reference other
tables but have no tables that reference them.) It's kind of like a
waterfall, you can't get data into some tables until it exists in other
tables so it's like the data sort of "trickles down" the structure. (This
analogy could also help to visualize how the use of the word "cascade"
describes the auto-propagation of a delete or update to the dependent
tables)
HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Todd Cranston-Cuebas <[EMAIL PROTECTED]> wrote on 08/29/2004 04:09:15 AM:
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 insert 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 insert the desccode, the jobdescription,
and jobcode data. 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 rows.
Can someone explain what I should do? Should I just change these fields
of data into NULL? I'm literally just trying to populate the tables
with enough data to run some test queries.
Any suggestions?
Todd
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
