Re: Data loading and foreign key constraints - help please
Thank you very much. I really appreciate your analogy to the waterfall. This helped me out tremendously. I was able to sort out the problem and all is now well! It appears that this wonderful little GUI tool the lets you create ER diagrams that auto-generate CREATE scripts assumes that you won't be including foreign keys in your entities. It expects you to build the relationship graphically and point out the primary keys, but it takes care of creating the foreign keys for you. My mistake. 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, jobcodeCHAR(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
Data loading and foreign key constraints - help please
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, jobcodeCHAR(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]
Re: Data loading
Michael Gale <[EMAIL PROTECTED]> wrote: > >I have a question about data loading using mysql 4.0.20. If you need to load > let's say 50,000 items into a database. > Now 50,000 is not a lot for a DB. > > So my question is would it be fast to load the file using the local infile which is > a security concern or should I be > using a loop with INSERTS ? LOAD DATA is alot faster than INSERTing data row by row because it uses bulk insert methodic. See http://dev.mysql.com/doc/mysql/en/INSERT.html and note that you can INSERT more than one row at once. Indexes are updated only a single time per each INSERT statement so inserting data in bulk will give you a great performance. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data loading
infile is a lot faster. Matthew McNicol Michael Gale wrote: Hello, I have a question about data loading using mysql 4.0.20. If you need to load let's say 50,000 items into a database. Now 50,000 is not a lot for a DB. So my question is would it be fast to load the file using the local infile which is a security concern or should I be using a loop with INSERTS ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data loading
Hello, I have a question about data loading using mysql 4.0.20. If you need to load let's say 50,000 items into a database. Now 50,000 is not a lot for a DB. So my question is would it be fast to load the file using the local infile which is a security concern or should I be using a loop with INSERTS ? -- Michael Gale Network Administrator Utilitran Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help : data loading speed
hi all.. i'm using mysql with php4 to develop an online ordering system. the problem: when it comes to save, retrieve/view and update data into mysql database, it takes a long time for the page to load. the problem occurs after we insert / key in data ( a lot types of data ), after we click the 'save' or 'edit' button. it also happens when there are many records to be loaded into the page for viewing. is it because of mysql has to deal with lots of data to be saved into the database? plus, i also did some checking before inserting the data since i am using some radiobuttons in the form. for example: $das_max_both is a radiobutton and returns value '1' if it is checked. $das_max_tweb is a textfield that will carry an inserted value by the user. i have a number of this type of checking in my codes. or is there any other simpler way to perform the data insertion and checking especially when it comes to insert, update and view? thanks.. -lisa- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie--Easy data loading question
> -Original Message- > From: Menard, Inc. Information Systems > Sent: Friday, May 24, 2002 3:44 PM > I figured it out but I'm a bit confused as to why it happened. > I'm running the load as user1. The file resides at > /home/user1/data/filename > Permissions: > filename 666 > data 777 > user1 700 > I had to change permissions on user1 to 777 in order for it to > work. If I'm > logged in as that user, why would I have to do that??? 1. I'm a newbie too, so don't be angry if my solution doesn't work. 2. I had similar problem. I changed permissions for my home folder on user mysql. It works. 3. I think, user in MySQL isn't the same as user in linux, despite the same name. I suppose, that there must be permissions for user 'mysql', and MySQL will do the rest. If I'm wrong, please correct me. Slavko Duda-Klimaszewski - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie--Easy data loading question
Dear Jake and MySQL listers, RE:>> I had to change permissions on user1 to 777 in order for it to work. If I'm logged in as that user, why would I have to do that??? I can't say for certian, though I would imagine that the mysqld actually does the processing on this type of a table load, i.e. it controls access to the mysql engine, not the user. This is why, whenever I want to do an import, I execute thusly: # mysql -u mysql -p < script_to_load_file Again, this may not be 100% requisite, but I'm into error prevention and this pretty much ensure that I won't bump into permissions issues. I don't lke setting a fill to 777 but prefer 755. Others can help with greater accuracy but this may be of some help for the future. Andrew Lietzow The ACL Group, Inc. On Friday 24 May 2002 08:44 am, Menard, Inc. Information Systems wrote: > I figured it out but I'm a bit confused as to why it happened. > I'm running the load as user1. The file resides at > /home/user1/data/filename Permissions: > filename 666 > data 777 > user1 700 > > I had to change permissions on user1 to 777 in order for it to work. If I'm > logged in as that user, why would I have to do that??? > > Thanks > Jake > > -Original Message- > From: Menard, Inc. Information Systems [mailto:[EMAIL PROTECTED]] > Sent: Friday, May 24, 2002 8:26 AM > To: Jay Blanchard; [EMAIL PROTECTED] > Subject: RE: Newbie--Easy data loading question > > > yes. I tried all of these methods: > 1) "//dir1//dir2//file" > 2) "/dir1/dir2/file" > running load from file dir using: > "./file" > ".//file" > > I also check the file permissions and it is all read/write. > I can't remember if I tried to run it using mysqls root yet...that would be > the next step, but I prefer not to go there. > > -Original Message----- > From: Jay Blanchard [mailto:[EMAIL PROTECTED]] > Sent: Friday, May 24, 2002 8:22 AM > To: 'Menard, Inc. Information Systems'; [EMAIL PROTECTED] > Subject: RE: Newbie--Easy data loading question > > > did you supply the /full/path/to/the/file/ ? > > -Original Message- > From: Menard, Inc. Information Systems [mailto:[EMAIL PROTECTED]] > Sent: Friday, May 24, 2002 8:15 AM > To: [EMAIL PROTECTED] > Subject: Newbie--Easy data loading question > > > Hello > > Forgive me for being a little naive but. > > I am trying to load a table with a text file using the load data infile > command in mysql. I can do it if I place the > text file in the db directory, but I want it to reside in a different dir. > I am getting the error 13: Can't get stat on > What am I doing wrong?? > > Thank you very much in advance for your help! > > Jake > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> Trouble > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Andrew Lietzow The ACL Group, Inc. 515-274-0300 v/f 515-710-1955 c - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie--Easy data loading question
Because mysql needs 'rx' permissions to read and search the directory. 755 should have been sufficient. Menard, Inc. Information Systems wrote: >I figured it out but I'm a bit confused as to why it happened. >I'm running the load as user1. The file resides at /home/user1/data/filename >Permissions: >filename 666 >data 777 >user1 700 > >I had to change permissions on user1 to 777 in order for it to work. If I'm >logged in as that user, why would I have to do that??? > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Newbie--Easy data loading question
I figured it out but I'm a bit confused as to why it happened. I'm running the load as user1. The file resides at /home/user1/data/filename Permissions: filename 666 data 777 user1 700 I had to change permissions on user1 to 777 in order for it to work. If I'm logged in as that user, why would I have to do that??? Thanks Jake -Original Message- From: Menard, Inc. Information Systems [mailto:[EMAIL PROTECTED]] Sent: Friday, May 24, 2002 8:26 AM To: Jay Blanchard; [EMAIL PROTECTED] Subject: RE: Newbie--Easy data loading question yes. I tried all of these methods: 1) "//dir1//dir2//file" 2) "/dir1/dir2/file" running load from file dir using: "./file" ".//file" I also check the file permissions and it is all read/write. I can't remember if I tried to run it using mysqls root yet...that would be the next step, but I prefer not to go there. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: Friday, May 24, 2002 8:22 AM To: 'Menard, Inc. Information Systems'; [EMAIL PROTECTED] Subject: RE: Newbie--Easy data loading question did you supply the /full/path/to/the/file/ ? -Original Message- From: Menard, Inc. Information Systems [mailto:[EMAIL PROTECTED]] Sent: Friday, May 24, 2002 8:15 AM To: [EMAIL PROTECTED] Subject: Newbie--Easy data loading question Hello Forgive me for being a little naive but. I am trying to load a table with a text file using the load data infile command in mysql. I can do it if I place the text file in the db directory, but I want it to reside in a different dir. I am getting the error 13: Can't get stat on What am I doing wrong?? Thank you very much in advance for your help! Jake - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Newbie--Easy data loading question
yes. I tried all of these methods: 1) "//dir1//dir2//file" 2) "/dir1/dir2/file" running load from file dir using: "./file" ".//file" I also check the file permissions and it is all read/write. I can't remember if I tried to run it using mysqls root yet...that would be the next step, but I prefer not to go there. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: Friday, May 24, 2002 8:22 AM To: 'Menard, Inc. Information Systems'; [EMAIL PROTECTED] Subject: RE: Newbie--Easy data loading question did you supply the /full/path/to/the/file/ ? -Original Message- From: Menard, Inc. Information Systems [mailto:[EMAIL PROTECTED]] Sent: Friday, May 24, 2002 8:15 AM To: [EMAIL PROTECTED] Subject: Newbie--Easy data loading question Hello Forgive me for being a little naive but. I am trying to load a table with a text file using the load data infile command in mysql. I can do it if I place the text file in the db directory, but I want it to reside in a different dir. I am getting the error 13: Can't get stat on What am I doing wrong?? Thank you very much in advance for your help! Jake - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Newbie--Easy data loading question
did you supply the /full/path/to/the/file/ ? -Original Message- From: Menard, Inc. Information Systems [mailto:[EMAIL PROTECTED]] Sent: Friday, May 24, 2002 8:15 AM To: [EMAIL PROTECTED] Subject: Newbie--Easy data loading question Hello Forgive me for being a little naive but. I am trying to load a table with a text file using the load data infile command in mysql. I can do it if I place the text file in the db directory, but I want it to reside in a different dir. I am getting the error 13: Can't get stat on What am I doing wrong?? Thank you very much in advance for your help! Jake - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie--Easy data loading question
Hello Forgive me for being a little naive but. I am trying to load a table with a text file using the load data infile command in mysql. I can do it if I place the text file in the db directory, but I want it to reside in a different dir. I am getting the error 13: Can't get stat on What am I doing wrong?? Thank you very much in advance for your help! Jake - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
