![]() | |
#1
| |||
| |||
|
#2
| |||
| |||
|
|
What do I need to do get rid of the SQL Error (1217):Cannot delete or update a parent row: a foreign key constraint fails from the following script? DROP TABLE IF EXISTS year2state; DROP TABLE IF EXISTS state2county; DROP TABLE IF EXISTS county2township; DROP TABLE IF EXISTS years; DROP TABLE IF EXISTS counties; DROP TABLE IF EXISTS townships; DROP TABLE IF EXISTS states; CREATE TABLE years ( yearid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, year CHAR(4)); CREATE TABLE states ( stateid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, state CHAR(10)); CREATE TABLE counties ( countyid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, county VARCHAR(20)); CREATE TABLE townships ( townshipid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, township VARCHAR(20)); CREATE TABLE year2state( yearid INT UNSIGNED NOT NULL, stateid INT UNSIGNED NOT NULL); CREATE TABLE state2county ( stateid INT UNSIGNED NOT NULL, countyid INT UNSIGNED NOT NULL); CREATE TABLE county2township ( countyid INT UNSIGNED NOT NULL, townshipid INT UNSIGNED NOT NULL); INSERT INTO states (state) VALUES ("Wisconsin"); INSERT INTO counties (county) VALUES ("Rock"); INSERT INTO years (year) VALUES ("1910"), ("1930"); INSERT INTO townships (township) VALUES ("Porter"); INSERT INTO year2state (yearid, stateid) VALUES (1,1), /* 1910 to Wisconsin */ (2,1) /* 1930 to Wisconsin */ ; INSERT INTO state2county (stateid, countyid) VALUES (1,1) /* Wisconsin to Rock */ ; INSERT INTO county2township (countyid, townshipid) VALUES (1, 1) /* Rock to Porter */ ; Thank you.... Bruce I have no problem execute you're script. |
#3
| |||
| |||
|
|
What do I need to do get rid of the SQL Error (1217):Cannot delete or update a parent row: a foreign key constraint fails from the following script? DROP TABLE IF EXISTS year2state; DROP TABLE IF EXISTS state2county; DROP TABLE IF EXISTS county2township; DROP TABLE IF EXISTS years; DROP TABLE IF EXISTS counties; DROP TABLE IF EXISTS townships; DROP TABLE IF EXISTS states; CREATE TABLE years ( yearid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, year CHAR(4)); CREATE TABLE states ( stateid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, state CHAR(10)); CREATE TABLE counties ( countyid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, county VARCHAR(20)); CREATE TABLE townships ( townshipid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, township VARCHAR(20)); CREATE TABLE year2state( yearid INT UNSIGNED NOT NULL, stateid INT UNSIGNED NOT NULL); CREATE TABLE state2county ( stateid INT UNSIGNED NOT NULL, countyid INT UNSIGNED NOT NULL); CREATE TABLE county2township ( countyid INT UNSIGNED NOT NULL, townshipid INT UNSIGNED NOT NULL); INSERT INTO states (state) VALUES ("Wisconsin"); INSERT INTO counties (county) VALUES ("Rock"); INSERT INTO years (year) VALUES ("1910"), ("1930"); INSERT INTO townships (township) VALUES ("Porter"); INSERT INTO year2state (yearid, stateid) VALUES (1,1), /* 1910 to Wisconsin */ (2,1) /* 1930 to Wisconsin */ ; INSERT INTO state2county (stateid, countyid) VALUES (1,1) /* Wisconsin to Rock */ ; INSERT INTO county2township (countyid, townshipid) VALUES (1, 1) /* Rock to Porter */ ; Thank you.... Bruce |
#4
| |||
| |||
|
|
bruce wrote: What do I need to do get rid of the SQL Error (1217):Cannot delete or update a parent row: a foreign key constraint fails from the following script? DROP TABLE IF EXISTS year2state; DROP TABLE IF EXISTS state2county; DROP TABLE IF EXISTS county2township; DROP TABLE IF EXISTS years; DROP TABLE IF EXISTS counties; DROP TABLE IF EXISTS townships; DROP TABLE IF EXISTS states; CREATE TABLE years ( yearid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, year CHAR(4)); CREATE TABLE states ( stateid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, state CHAR(10)); CREATE TABLE counties ( countyid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, county VARCHAR(20)); CREATE TABLE townships ( townshipid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, township VARCHAR(20)); CREATE TABLE year2state( yearid INT UNSIGNED NOT NULL, stateid INT UNSIGNED NOT NULL); CREATE TABLE state2county ( stateid INT UNSIGNED NOT NULL, countyid INT UNSIGNED NOT NULL); CREATE TABLE county2township ( countyid INT UNSIGNED NOT NULL, townshipid INT UNSIGNED NOT NULL); INSERT INTO states (state) VALUES ("Wisconsin"); INSERT INTO counties (county) VALUES ("Rock"); INSERT INTO years (year) VALUES ("1910"), ("1930"); INSERT INTO townships (township) VALUES ("Porter"); INSERT INTO year2state (yearid, stateid) VALUES (1,1), * * /* 1910 to Wisconsin */ (2,1) * * */* 1930 to Wisconsin */ ; INSERT INTO state2county (stateid, countyid) VALUES (1,1) * * */* Wisconsin to Rock **/ ; INSERT INTO county2township (countyid, townshipid) VALUES (1, 1) * * /* Rock to Porter * * **/ ; Thank you.... Bruce I have no problem execute you're script. It seems to me that you have an undeleted table with foreign key(s) in. It would help if you took you're script and execute it line by line to locate the error. /henrik |
#5
| |||
| |||
|
|
On 1/12/2011 2:10 PM, bruce wrote: What do I need to do get rid of the SQL Error (1217):Cannot delete or update a parent row: a foreign key constraint fails from the following script? DROP TABLE IF EXISTS year2state; DROP TABLE IF EXISTS state2county; DROP TABLE IF EXISTS county2township; DROP TABLE IF EXISTS years; DROP TABLE IF EXISTS counties; DROP TABLE IF EXISTS townships; DROP TABLE IF EXISTS states; CREATE TABLE years ( yearid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, year CHAR(4)); CREATE TABLE states ( stateid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, state CHAR(10)); CREATE TABLE counties ( countyid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, county VARCHAR(20)); CREATE TABLE townships ( townshipid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, township VARCHAR(20)); CREATE TABLE year2state( yearid INT UNSIGNED NOT NULL, stateid INT UNSIGNED NOT NULL); CREATE TABLE state2county ( stateid INT UNSIGNED NOT NULL, countyid INT UNSIGNED NOT NULL); CREATE TABLE county2township ( countyid INT UNSIGNED NOT NULL, townshipid INT UNSIGNED NOT NULL); INSERT INTO states (state) VALUES ("Wisconsin"); INSERT INTO counties (county) VALUES ("Rock"); INSERT INTO years (year) VALUES ("1910"), ("1930"); INSERT INTO townships (township) VALUES ("Porter"); INSERT INTO year2state (yearid, stateid) VALUES (1,1), * * /* 1910 to Wisconsin */ (2,1) * * */* 1930 to Wisconsin */ ; INSERT INTO state2county (stateid, countyid) VALUES (1,1) * * */* Wisconsin to Rock **/ ; INSERT INTO county2township (countyid, townshipid) VALUES (1, 1) * * /* Rock to Porter * * **/ ; Thank you.... Bruce Bruce, Are you sure this is the script you are running? *Which statement gets the error? The reason I ask is you the 1217 error indicates a violation of a foreign key constraint, but you haven't defined any foreign keys. Maybe it's failing on a DROP TABLE because a previous foreign key constraint existed? -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== |
#6
| |||
| |||
|
|
On Jan 12, 4:16*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: On 1/12/2011 2:10 PM, bruce wrote: What do I need to do get rid of the SQL Error (1217):Cannot delete or update a parent row: a foreign key constraint fails from the following script? DROP TABLE IF EXISTS year2state; DROP TABLE IF EXISTS state2county; DROP TABLE IF EXISTS county2township; DROP TABLE IF EXISTS years; DROP TABLE IF EXISTS counties; DROP TABLE IF EXISTS townships; DROP TABLE IF EXISTS states; CREATE TABLE years ( yearid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, year CHAR(4)); CREATE TABLE states ( stateid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, state CHAR(10)); CREATE TABLE counties ( countyid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, county VARCHAR(20)); CREATE TABLE townships ( townshipid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, township VARCHAR(20)); CREATE TABLE year2state( yearid INT UNSIGNED NOT NULL, stateid INT UNSIGNED NOT NULL); CREATE TABLE state2county ( stateid INT UNSIGNED NOT NULL, countyid INT UNSIGNED NOT NULL); CREATE TABLE county2township ( countyid INT UNSIGNED NOT NULL, townshipid INT UNSIGNED NOT NULL); INSERT INTO states (state) VALUES ("Wisconsin"); INSERT INTO counties (county) VALUES ("Rock"); INSERT INTO years (year) VALUES ("1910"), ("1930"); INSERT INTO townships (township) VALUES ("Porter"); INSERT INTO year2state (yearid, stateid) VALUES (1,1), * * /* 1910 to Wisconsin */ (2,1) * * */* 1930 to Wisconsin */ ; INSERT INTO state2county (stateid, countyid) VALUES (1,1) * * */* Wisconsin to Rock **/ ; INSERT INTO county2township (countyid, townshipid) VALUES (1, 1) * * /* Rock to Porter * * **/ ; Thank you.... Bruce Bruce, Are you sure this is the script you are running? *Which statement gets the error? The reason I ask is you the 1217 error indicates a violation of a foreign key constraint, but you haven't defined any foreign keys. Maybe it's failing on a DROP TABLE because a previous foreign key constraint existed? -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'm running it under HeidiSQL. What is the syntax for running it from the command line? Thanks.. Bruce |
#7
| |||
| |||
|
|
On Jan 12, 3:47 pm, Henrik Hartig <henrikhar... (AT) varmmail (DOT) dk> wrote: When I run, using HeidiSQL, I get the error following Don't know HeidiSQL DROP TABLE IF EXISTS states; So, I assume it's somehow related to this DROP TABLE. If I manually DROP states, using HeidiSQL, then run the script, it works fine. If I run it a 2nd time, without dropping the states table, it fails, as before.. Did you try to run the script a second time?? Now I have. With no problem. You must have created table states with a |
|
Thank you for the response. (Mange Tak) aha du er ogsċ dansker :-) Bruce |
#8
| |||
| |||
|
|
On 1/12/2011 2:10 PM, bruce wrote: What do I need to do get rid of the SQL Error (1217):Cannot delete or update a parent row: a foreign key constraint fails from the following script? DROP TABLE IF EXISTS year2state; DROP TABLE IF EXISTS state2county; DROP TABLE IF EXISTS county2township; DROP TABLE IF EXISTS years; DROP TABLE IF EXISTS counties; DROP TABLE IF EXISTS townships; DROP TABLE IF EXISTS states; CREATE TABLE years ( yearid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, year CHAR(4)); CREATE TABLE states ( stateid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, state CHAR(10)); CREATE TABLE counties ( countyid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, county VARCHAR(20)); CREATE TABLE townships ( townshipid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, township VARCHAR(20)); CREATE TABLE year2state( yearid INT UNSIGNED NOT NULL, stateid INT UNSIGNED NOT NULL); CREATE TABLE state2county ( stateid INT UNSIGNED NOT NULL, countyid INT UNSIGNED NOT NULL); CREATE TABLE county2township ( countyid INT UNSIGNED NOT NULL, townshipid INT UNSIGNED NOT NULL); INSERT INTO states (state) VALUES ("Wisconsin"); INSERT INTO counties (county) VALUES ("Rock"); INSERT INTO years (year) VALUES ("1910"), ("1930"); INSERT INTO townships (township) VALUES ("Porter"); INSERT INTO year2state (yearid, stateid) VALUES (1,1), /* 1910 to Wisconsin */ (2,1) /* 1930 to Wisconsin */ ; INSERT INTO state2county (stateid, countyid) VALUES (1,1) /* Wisconsin to Rock */ ; INSERT INTO county2township (countyid, townshipid) VALUES (1, 1) /* Rock to Porter */ ; Thank you.... Bruce Bruce, Are you sure this is the script you are running? Which statement gets the error? The reason I ask is you the 1217 error indicates a violation of a foreign key constraint, but you haven't defined any foreign keys. Maybe it's failing on a DROP TABLE because a previous foreign key constraint existed? My point to |
#9
| |||
| |||
|
|
On Jan 12, 4:16*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: On 1/12/2011 2:10 PM, bruce wrote: What do I need to do get rid of the SQL Error (1217):Cannot delete or update a parent row: a foreign key constraint fails from the following script? DROP TABLE IF EXISTS year2state; DROP TABLE IF EXISTS state2county; DROP TABLE IF EXISTS county2township; DROP TABLE IF EXISTS years; DROP TABLE IF EXISTS counties; DROP TABLE IF EXISTS townships; DROP TABLE IF EXISTS states; CREATE TABLE years ( yearid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, year CHAR(4)); CREATE TABLE states ( stateid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, state CHAR(10)); CREATE TABLE counties ( countyid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, county VARCHAR(20)); CREATE TABLE townships ( townshipid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, township VARCHAR(20)); CREATE TABLE year2state( yearid INT UNSIGNED NOT NULL, stateid INT UNSIGNED NOT NULL); CREATE TABLE state2county ( stateid INT UNSIGNED NOT NULL, countyid INT UNSIGNED NOT NULL); CREATE TABLE county2township ( countyid INT UNSIGNED NOT NULL, townshipid INT UNSIGNED NOT NULL); INSERT INTO states (state) VALUES ("Wisconsin"); INSERT INTO counties (county) VALUES ("Rock"); INSERT INTO years (year) VALUES ("1910"), ("1930"); INSERT INTO townships (township) VALUES ("Porter"); INSERT INTO year2state (yearid, stateid) VALUES (1,1), * * /* 1910 to Wisconsin */ (2,1) * * */* 1930 to Wisconsin */ ; INSERT INTO state2county (stateid, countyid) VALUES (1,1) * * */* Wisconsin to Rock **/ ; INSERT INTO county2township (countyid, townshipid) VALUES (1, 1) * * /* Rock to Porter * * **/ ; Thank you.... Bruce Bruce, Are you sure this is the script you are running? *Which statement gets the error? The reason I ask is you the 1217 error indicates a violation of a foreign key constraint, but you haven't defined any foreign keys. Maybe it's failing on a DROP TABLE because a previous foreign key constraint existed? -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'm running it under HeidiSQL. What is the syntax for running it from the command line? Thanks.. Bruce |
#10
| |||
| |||
|
|
What do I need to do get rid of the SQL Error (1217):Cannot delete or update a parent row: a foreign key constraint fails from the following script? |
![]() |
| Thread Tools | |
| Display Modes | |
| |