How do I find out what foreign keys depend on this roy?

Hi, Using PHP 4, MySQL 4, I'm getting an error when executing an SQL
statement:

Statement failed "DELETE FROM USERS WHERE USER_ID = 17" - Cannot delete
or update a parent row: a foreign key constraint fails

How can I query the SQL database to figure out what parent row the
statement is complaining about?

Thanks,-

Re: How do I find out what foreign keys depend on this roy?

laredotornado@zipmail.com wrote:
> Hi, Using PHP 4, MySQL 4, I'm getting an error when executing an SQL
> statement:
>
> Statement failed "DELETE FROM USERS WHERE USER_ID = 17" - Cannot delete
> or update a parent row: a foreign key constraint fails
>
> How can I query the SQL database to figure out what parent row the
> statement is complaining about?
>
> Thanks,-
>
For those encountering the problem " ERROR 1216: Cannot add or update
a child row: a foreign key constraint fails", it actually means what it
says! Some row in the child does not comply with the constraint, correct
the problem.
You find the rows like this:
select child.id from child left join parent on
(child.parent_id=parent.id) where child.id is not null and parent.id is
null;

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-co nstraints.html


Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.phpnet.us
____________________________________________________________ ______________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)

Re: How do I find out what foreign keys depend on this roy?

Thanks but the error actually says "parent row" and not "child row".
So how do I deal with taht?

IchBin wrote:
> laredotornado@zipmail.com wrote:
> > Hi, Using PHP 4, MySQL 4, I'm getting an error when executing an SQL
> > statement:
> >
> > Statement failed "DELETE FROM USERS WHERE USER_ID = 17" - Cannot delete
> > or update a parent row: a foreign key constraint fails
> >
> > How can I query the SQL database to figure out what parent row the
> > statement is complaining about?
> >
> > Thanks,-
> >
> For those encountering the problem " ERROR 1216: Cannot add or update
> a child row: a foreign key constraint fails", it actually means what it
> says! Some row in the child does not comply with the constraint, correct
> the problem.
> You find the rows like this:
> select child.id from child left join parent on
> (child.parent_id=parent.id) where child.id is not null and parent.id is
> null;
>
> http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-co nstraints.html
>
>
> Thanks in Advance...
> IchBin, Pocono Lake, Pa, USA http://weconsultants.phpnet.us
> ____________________________________________________________ ______________
>
> 'If there is one, Knowledge is the "Fountain of Youth"'
> -William E. Taylor, Regular Guy (1952-)

Re: How do I find out what foreign keys depend on this roy?

On 25 Jul 2006 08:19:30 -0700, laredotornado@zipmail.com wrote:

>Hi, Using PHP 4, MySQL 4, I'm getting an error when executing an SQL
>statement:
>
>Statement failed "DELETE FROM USERS WHERE USER_ID = 17" - Cannot delete
>or update a parent row: a foreign key constraint fails
>
>How can I query the SQL database to figure out what parent row the
>statement is complaining about?

It seems the only way to list foreign keys in MySQL is using SHOW CREATE
TABLE, which doesn't help you much.

SHOW INNODB STATUS shows information on recent foreign key errors, but that'll
all be mixed together with other sessions.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Re: How do I find out what foreign keys depend on this roy?

wrote in message
news:1153840770.299268.49940@b28g2000cwb.googlegroups.com...
> Hi, Using PHP 4, MySQL 4, I'm getting an error when executing an SQL
> statement:
>
> Statement failed "DELETE FROM USERS WHERE USER_ID = 17" - Cannot delete
> or update a parent row: a foreign key constraint fails
>
> How can I query the SQL database to figure out what parent row the
> statement is complaining about?
>
> Thanks,-


are both sides of the relationship indexed? from what i have read the
InnoDB engine is the first to need that rule and if you upgraded from
another engine ( if thats even possible ) perhaps it was over looked. i am
new so if anyone here corrects me i would listen to them.

Frank

Re: How do I find out what foreign keys depend on this roy?

"Frankly" wrote in message
news:0Nsxg.3398$S_1.432@trndny05...
> are both sides of the relationship indexed? from what i have read the
> InnoDB engine is the first to need that rule and if you upgraded from
> another engine ( if thats even possible ) perhaps it was over looked. i
> am new so if anyone here corrects me i would listen to them.
>
> Frank


Hey Frank.

you just reminded yourself that perhaps this might be something that has
been causing you problems.
i would go check it out.
will do..
thanks Frank
No Problem.

Frank

and i know i have read something to the point that in mysql foreign keys are
not needed.