(1452, 'Cannot add or update a child row: a foreign key constraint fails (`survey/surveydata`, CONSTRAINT `surveydata_ibfk_1` FOREIGN KEY (`occupationID`) REFERENCES `occupationdata` (`occupationID`))')
Posted by: Craig L
Date: December 24, 2010 08:03AM
Date: December 24, 2010 08:03AM
drop database if exists survey;
create database survey;
use survey;
# create the children (surveyData, occupationData, reachData and involvementData) before the parents!
create table occupationData (
occupationID int AUTO_INCREMENT PRIMARY KEY,
occupationDes varchar(30) NOT NULL
) ENGINE=INNODB;
create table reachData (
reachID int AUTO_INCREMENT PRIMARY KEY,
reachDes varchar(30) NOT NULL
) ENGINE=INNODB;
create table involvementData (
involvementID varchar(3) PRIMARY KEY,
involvementDes varchar(30) NOT NULL
) ENGINE=INNODB;
create table surveyData (
summaryID int AUTO_INCREMENT PRIMARY KEY,
name varchar(20) NOT NULL,
email varchar(30) NOT NULL,
occupationID int NOT NULL,
rating int NOT NULL,
reachID int NOT NULL,
involvementID varchar(3) NOT NULL,
comments varchar(200) NOT NULL,
browser varchar(50) NOT NULL,
FOREIGN KEY (occupationID) REFERENCES occupationData (occupationID) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (reachID) REFERENCES reachData (reachID) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (involvementID) REFERENCES involvementData (involvementID) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=INNODB;
# constraint means must insert reachDes into reachData
insert into reachData (reachDes) values ('Typed the URL directly');
insert into reachData (reachDes) values ('Site is bookmarked');
insert into reachData (reachDes) values ('A search engine');
insert into reachData (reachDes) values ('A link from another site');
insert into reachData (reachDes) values ('From a book');
insert into reachData (reachDes) values ('Other');
# constraint means must insert occupationDes into occupationData
insert into occupationData (occupationDes) values ('undergraduate student');
insert into occupationData (occupationDes) values ('Postgraduate student (MSc)');
insert into occupationData (occupationDes) values ('Postgraduate student (PhD)');
insert into occupationData (occupationDes) values ('Lab assistant');
insert into occupationData (occupationDes) values ('Technician');
insert into occupationData (occupationDes) values ('Lecturer');
insert into occupationData (occupationDes) values ('Other');
# constraint means must insert involvementID after inserting involvementDes
insert into involvementData (involvementID,involvementDes) values ('des','Website Design');
insert into involvementData (involvementID,involvementDes) values ('svr','Web Server Administration');
insert into involvementData (involvementID,involvementDes) values ('com','Electronic Commerce');
insert into involvementData (involvementID,involvementDes) values ('mkt','Web Marketing/Advertising');
insert into involvementData (involvementID,involvementDes) values ('edu','Web-related Education');
----------------------------------------------------------------------------------
import cgi # pythons common gateway interface
import cgitb; cgitb.enable() #sends runtime errors to browser
import os
import sys
import re
import time
import string
import MySQLdb
import checker
http = "Content-type:text/html"
title = os.environ['SCRIPT_NAME']
head="<html><head><title>%s</title></head><body>"
foot="</body></html>"
if os.environ['REQUEST_METHOD']=='GET':
# user loads web page for first time so sees an empty form with no error messages
checker.plainTemplateFile("surveyValidatedFileTemplate.html")
else:
# POST form submission
formErrors = { } # empty dictionary to be used for feedback on form
fillInForm = { } # empty dictioanry to be used to fill in form with entries if required by form errors
# check that the form submission is not too large
form = cgi.FieldStorage()
# Since the "what is your occupation" list was saved as
# a number, we need a dictionary to translate it back to English:
# have removed the 0 : "" dictionary entry
occupations = { 1 : "Undergraduate student",
2 : "Postgraduate student (MSc)",
3 : "Postgraduate student (PhD)",
4 : "Lab assistant",
5 : "Technician",
6 : "Lecturer",
7 : "Other" }
# A dictionary is also needed for the "how you reached this site" list
# have removed the 0 : "" dictionary entry
howreaches = { 1 : "Typed the URL directly",
2 : "Site is bookmarked",
3 : "A search engine",
4 : "A link from another site",
5 : "From a book",
6 : "Other" }
userip = os.environ['REMOTE_ADDR'];
# identify browser
if os.environ.has_key('HTTP_USER_AGENT'):
agent = os.environ['HTTP_USER_AGENT']
else:
agent = "an unknown browser"
if re.search(r'MSIE',agent,re.IGNORECASE ):
browser = "Internet Explorer"
elif re.search(r'Mozilla',agent,re.IGNORECASE):
browser = "Firefox"
else:
browser = "Other"
(formErrors['nameMsg'], name_val) = checker.nameError(form,'name')
fillInForm['name'] = cgi.escape(name_val,True)
if formErrors['nameMsg']:
# customise message if required (eg translate into local language)
#print formErrors['nameMsg']
pass
(formErrors['emailMsg'], email_val) = checker.emailError(form,'email')
fillInForm['email'] = cgi.escape(email_val,True)
if formErrors['emailMsg']: # this will crash if you haven't used a validation function for email
# customise message if required (eg translate into local language)
#print formErrors['emailMsg']
formErrors['emailMsg'] = "Please Enter an email"
pass
(formErrors['occupationMsg'], occupation) = checker.mandatoryOneError(form,'occupation',['1','2','3','4','5','6','7'])
if formErrors['occupationMsg']:
# customise message if required (eg make less generic)
#print formErrors['occupation_no']
formErrors['occupationMsg'] = "Please select an occupation"
pass
else:
occupation_no = int(occupation)
occupation_val = occupations[occupation_no]
fillInForm["occupation%s"%occupation] = " selected='selected' "
pass
(formErrors['howreachMsg'], howreach) = checker.mandatoryOneError(form,'howreach',['1','2','3','4','5','6'])
if formErrors['howreachMsg']:
# customise message if required (eg make less generic)
#print formErrors['howreach_no']
formErrors['howreachMsg'] = "Please select howreach"
pass
else:
howreach_no = int(howreach)
howreach_val = howreaches[howreach_no]
fillInForm["howreach%s"%howreach] = " selected='selected' "
pass
(formErrors['ratingMsg'], rating)= checker.mandatoryOneError(form,'rating',['1','2','3','4','5'])
if formErrors['ratingMsg']:
# customise message if required
#print formErrors['ratingMsg']
formErrors['ratingMsg'] = "Please rate the website."
pass
else:
rating_no = int(rating)
fillInForm["rating%s"%rating] = " selected='selected' "
boxes = { "des" : "Website Design",\
"svr" : "Web Server Administration",\
"com" : "Electronic Commerce",\
"mkt" : "Web Marketing/Advertising",\
"edu" : "Web-Related Education" }
involvements_codes=[]
for mykey in boxes.keys():
if form.has_key(mykey):
#print " <strong>",boxes[mykey],"</strong>";
involvements_codes.append(mykey)
fillInForm[mykey] = " checked='checked' "
if form.has_key('comments'):
comments_val = form['comments'].value
fillInForm['comments'] = comments_val # use a function to make safe any <, > and "
else:
comments_val = ""
if len("".join(formErrors.values()))>0:
# add form values to dictionary to fill in form with previous entries
# comment out the next line and see what happens (MD)
#formErrors.update(fillInForm) # might as well prepoulate form too so add form entries to placeholders
checker.processTemplateFile("surveyValidatedFileTemplate.html",formErrors)
else:
# valid form submission so add to survey records
print http
print
print head%title
connection = MySQLdb.connect( db = "survey", host="localhost", port=3306, user="#####", passwd="#####" )
# make sure you use the correct database, user, port and passwd in the connection
cursor = connection.cursor()
sql1 = "insert into surveydata (name,email,occupationID,reachID,rating,involvementID,comments,browser) values (%s,%s,%s,%s,%s,%s,%s,%s);"
#print sql1;
cursor.execute(sql1,(name_val,email_val,occupation_val,howreach_val,rating_no,occupation_no,comments_val,browser))
connection.commit()
#print to page
print "<h3>Results</h3><table>"
print "<tr><td>Your name</td><td> <strong>",cgi.escape(name_val,True),"</strong></td></tr>"
print "<tr><td> Email</td><td> <strong>",cgi.escape(email_val,True),"</strong></td></tr>"
print "<tr><td> Your occupation</td><td> <strong>",cgi.escape(occupation_val,True),"</strong></td></tr>"
print "<tr><td> How you reached the site</td><td> <strong>",cgi.escape(howreach_val,True),"</strong></td></tr>"
print "<tr><td> Your browser address</td><td> <strong>",cgi.escape(userip,True),"</strong></td></tr>"
print "<tr><td> Your browser</td><td> <strong>",cgi.escape(browser,True),"</strong></td></tr>"
print "<tr><td> How you rated the site</td><td> <strong>",rating_no,"</strong> (1=poor, 5=excellent)</td></tr>"
print "</table>"
print "As a ",occupations[occupation_no]," you're also involved in:";
for mykey in involvements_codes:
print " <strong>",boxes[mykey],"</strong>";
if len(comments_val)>0:
print "<p>Your comments were:<strong>",cgi.escape(comments_val,True),"</strong><p>"
print """
<p><em>Thank you for your feedback.</em></p>
"""
print foot
----------------------------------------------------------------------------------
Having serious issues and was curious if anyone knew the solution. The top file is the text file I have used to create the database through command prompt for MySQL. Also have left the shebang out on the post for obvious reasons.
create database survey;
use survey;
# create the children (surveyData, occupationData, reachData and involvementData) before the parents!
create table occupationData (
occupationID int AUTO_INCREMENT PRIMARY KEY,
occupationDes varchar(30) NOT NULL
) ENGINE=INNODB;
create table reachData (
reachID int AUTO_INCREMENT PRIMARY KEY,
reachDes varchar(30) NOT NULL
) ENGINE=INNODB;
create table involvementData (
involvementID varchar(3) PRIMARY KEY,
involvementDes varchar(30) NOT NULL
) ENGINE=INNODB;
create table surveyData (
summaryID int AUTO_INCREMENT PRIMARY KEY,
name varchar(20) NOT NULL,
email varchar(30) NOT NULL,
occupationID int NOT NULL,
rating int NOT NULL,
reachID int NOT NULL,
involvementID varchar(3) NOT NULL,
comments varchar(200) NOT NULL,
browser varchar(50) NOT NULL,
FOREIGN KEY (occupationID) REFERENCES occupationData (occupationID) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (reachID) REFERENCES reachData (reachID) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (involvementID) REFERENCES involvementData (involvementID) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=INNODB;
# constraint means must insert reachDes into reachData
insert into reachData (reachDes) values ('Typed the URL directly');
insert into reachData (reachDes) values ('Site is bookmarked');
insert into reachData (reachDes) values ('A search engine');
insert into reachData (reachDes) values ('A link from another site');
insert into reachData (reachDes) values ('From a book');
insert into reachData (reachDes) values ('Other');
# constraint means must insert occupationDes into occupationData
insert into occupationData (occupationDes) values ('undergraduate student');
insert into occupationData (occupationDes) values ('Postgraduate student (MSc)');
insert into occupationData (occupationDes) values ('Postgraduate student (PhD)');
insert into occupationData (occupationDes) values ('Lab assistant');
insert into occupationData (occupationDes) values ('Technician');
insert into occupationData (occupationDes) values ('Lecturer');
insert into occupationData (occupationDes) values ('Other');
# constraint means must insert involvementID after inserting involvementDes
insert into involvementData (involvementID,involvementDes) values ('des','Website Design');
insert into involvementData (involvementID,involvementDes) values ('svr','Web Server Administration');
insert into involvementData (involvementID,involvementDes) values ('com','Electronic Commerce');
insert into involvementData (involvementID,involvementDes) values ('mkt','Web Marketing/Advertising');
insert into involvementData (involvementID,involvementDes) values ('edu','Web-related Education');
----------------------------------------------------------------------------------
import cgi # pythons common gateway interface
import cgitb; cgitb.enable() #sends runtime errors to browser
import os
import sys
import re
import time
import string
import MySQLdb
import checker
http = "Content-type:text/html"
title = os.environ['SCRIPT_NAME']
head="<html><head><title>%s</title></head><body>"
foot="</body></html>"
if os.environ['REQUEST_METHOD']=='GET':
# user loads web page for first time so sees an empty form with no error messages
checker.plainTemplateFile("surveyValidatedFileTemplate.html")
else:
# POST form submission
formErrors = { } # empty dictionary to be used for feedback on form
fillInForm = { } # empty dictioanry to be used to fill in form with entries if required by form errors
# check that the form submission is not too large
form = cgi.FieldStorage()
# Since the "what is your occupation" list was saved as
# a number, we need a dictionary to translate it back to English:
# have removed the 0 : "" dictionary entry
occupations = { 1 : "Undergraduate student",
2 : "Postgraduate student (MSc)",
3 : "Postgraduate student (PhD)",
4 : "Lab assistant",
5 : "Technician",
6 : "Lecturer",
7 : "Other" }
# A dictionary is also needed for the "how you reached this site" list
# have removed the 0 : "" dictionary entry
howreaches = { 1 : "Typed the URL directly",
2 : "Site is bookmarked",
3 : "A search engine",
4 : "A link from another site",
5 : "From a book",
6 : "Other" }
userip = os.environ['REMOTE_ADDR'];
# identify browser
if os.environ.has_key('HTTP_USER_AGENT'):
agent = os.environ['HTTP_USER_AGENT']
else:
agent = "an unknown browser"
if re.search(r'MSIE',agent,re.IGNORECASE ):
browser = "Internet Explorer"
elif re.search(r'Mozilla',agent,re.IGNORECASE):
browser = "Firefox"
else:
browser = "Other"
(formErrors['nameMsg'], name_val) = checker.nameError(form,'name')
fillInForm['name'] = cgi.escape(name_val,True)
if formErrors['nameMsg']:
# customise message if required (eg translate into local language)
#print formErrors['nameMsg']
pass
(formErrors['emailMsg'], email_val) = checker.emailError(form,'email')
fillInForm['email'] = cgi.escape(email_val,True)
if formErrors['emailMsg']: # this will crash if you haven't used a validation function for email
# customise message if required (eg translate into local language)
#print formErrors['emailMsg']
formErrors['emailMsg'] = "Please Enter an email"
pass
(formErrors['occupationMsg'], occupation) = checker.mandatoryOneError(form,'occupation',['1','2','3','4','5','6','7'])
if formErrors['occupationMsg']:
# customise message if required (eg make less generic)
#print formErrors['occupation_no']
formErrors['occupationMsg'] = "Please select an occupation"
pass
else:
occupation_no = int(occupation)
occupation_val = occupations[occupation_no]
fillInForm["occupation%s"%occupation] = " selected='selected' "
pass
(formErrors['howreachMsg'], howreach) = checker.mandatoryOneError(form,'howreach',['1','2','3','4','5','6'])
if formErrors['howreachMsg']:
# customise message if required (eg make less generic)
#print formErrors['howreach_no']
formErrors['howreachMsg'] = "Please select howreach"
pass
else:
howreach_no = int(howreach)
howreach_val = howreaches[howreach_no]
fillInForm["howreach%s"%howreach] = " selected='selected' "
pass
(formErrors['ratingMsg'], rating)= checker.mandatoryOneError(form,'rating',['1','2','3','4','5'])
if formErrors['ratingMsg']:
# customise message if required
#print formErrors['ratingMsg']
formErrors['ratingMsg'] = "Please rate the website."
pass
else:
rating_no = int(rating)
fillInForm["rating%s"%rating] = " selected='selected' "
boxes = { "des" : "Website Design",\
"svr" : "Web Server Administration",\
"com" : "Electronic Commerce",\
"mkt" : "Web Marketing/Advertising",\
"edu" : "Web-Related Education" }
involvements_codes=[]
for mykey in boxes.keys():
if form.has_key(mykey):
#print " <strong>",boxes[mykey],"</strong>";
involvements_codes.append(mykey)
fillInForm[mykey] = " checked='checked' "
if form.has_key('comments'):
comments_val = form['comments'].value
fillInForm['comments'] = comments_val # use a function to make safe any <, > and "
else:
comments_val = ""
if len("".join(formErrors.values()))>0:
# add form values to dictionary to fill in form with previous entries
# comment out the next line and see what happens (MD)
#formErrors.update(fillInForm) # might as well prepoulate form too so add form entries to placeholders
checker.processTemplateFile("surveyValidatedFileTemplate.html",formErrors)
else:
# valid form submission so add to survey records
print http
print head%title
connection = MySQLdb.connect( db = "survey", host="localhost", port=3306, user="#####", passwd="#####" )
# make sure you use the correct database, user, port and passwd in the connection
cursor = connection.cursor()
sql1 = "insert into surveydata (name,email,occupationID,reachID,rating,involvementID,comments,browser) values (%s,%s,%s,%s,%s,%s,%s,%s);"
#print sql1;
cursor.execute(sql1,(name_val,email_val,occupation_val,howreach_val,rating_no,occupation_no,comments_val,browser))
connection.commit()
#print to page
print "<h3>Results</h3><table>"
print "<tr><td>Your name</td><td> <strong>",cgi.escape(name_val,True),"</strong></td></tr>"
print "<tr><td> Email</td><td> <strong>",cgi.escape(email_val,True),"</strong></td></tr>"
print "<tr><td> Your occupation</td><td> <strong>",cgi.escape(occupation_val,True),"</strong></td></tr>"
print "<tr><td> How you reached the site</td><td> <strong>",cgi.escape(howreach_val,True),"</strong></td></tr>"
print "<tr><td> Your browser address</td><td> <strong>",cgi.escape(userip,True),"</strong></td></tr>"
print "<tr><td> Your browser</td><td> <strong>",cgi.escape(browser,True),"</strong></td></tr>"
print "<tr><td> How you rated the site</td><td> <strong>",rating_no,"</strong> (1=poor, 5=excellent)</td></tr>"
print "</table>"
print "As a ",occupations[occupation_no]," you're also involved in:";
for mykey in involvements_codes:
print " <strong>",boxes[mykey],"</strong>";
if len(comments_val)>0:
print "<p>Your comments were:<strong>",cgi.escape(comments_val,True),"</strong><p>"
print """
<p><em>Thank you for your feedback.</em></p>
"""
print foot
----------------------------------------------------------------------------------
Having serious issues and was curious if anyone knew the solution. The top file is the text file I have used to create the database through command prompt for MySQL. Also have left the shebang out on the post for obvious reasons.
Subject
Written By
Posted
(1452, 'Cannot add or update a child row: a foreign key constraint fails (`survey/surveydata`, CONSTRAINT `surveydata_ibfk_1` FOREIGN KEY (`occupationID`) REFERENCES `occupationdata` (`occupationID`))')
December 24, 2010 08:03AM
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.