525: Failure to satisfy referential constraint scv
A DBA got error 525 ("Failure to satisfy referential constraint", ISAM 111) when adding a foreign key from scv_asn_detail to scv_asn_header, and asked how to quickly find the offending rows. Suggestions: enable violation/diagnostic tables (START VIOLATIONS TABLE) so bad rows are captured during the ALTER, or simply run SELECT * FROM scv_asn_detail WHERE asn_key NOT IN (SELECT asn_key FROM scv_asn_header). Another poster offered a system-catalog query listing referential constraints and their enabled state up to six levels deep. The original poster said he'd use violation tables; no final outcome was posted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is there an easy quick way to determine the offending row(s) in the
constraint error?
alter table "dbascv1".scv_asn_detail add constraint (foreign
key (asn_key) references "dbascv1".scv_asn_header on delete
cascade constraint "dbascv1".scv_asn_detail_fk01);
525: Failure to satisfy referential constraint scv_asn_detail_fk01.
111: ISAM error: no record found.
Thanks,
*******************************************************************
Ernie Knox
IT Database Administrator Specialist
Sears Holdings - BU: I & T Group
3333 Beverly Rd., B4-266A
Hoffman Estates, IL. 60179
Office: (847) 286-5735
Email: Ernest.Knox@searshc.com
Blackberry: 2244650553@messaging.sprintpcs.com
<mailto:2244650553@messaging.sprintpcs.com>
Page via Skytel: 2244650553@sprint.skytel.com
<mailto:2244650553@sprint.skytel.com>
Informix or MySQL Primary: 9110210@skytel.com
<mailto:9110210@skytel.com>
Informix or MySQL Secondary: 7276872@skytel.com
<mailto:7276872@skytel.com>
" Yes we can make a Change! "
" It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS!
"
" Lets not forget - GO Pistons and Red Wings! "
GSU
*******************************************************************
Use violation tables ?
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Knox,
Ernest
Sent: Saturday, August 14, 2010 5:56 PM
To: ids@iiug.org
Subject: 525: Failure to satisfy referential constraint.... [20924]
Is there an easy quick way to determine the offending row(s) in the
constraint error?
alter table "dbascv1".scv_asn_detail add constraint (foreign
key (asn_key) references "dbascv1".scv_asn_header on delete
cascade constraint "dbascv1".scv_asn_detail_fk01);
525: Failure to satisfy referential constraint scv_asn_detail_fk01.
111: ISAM error: no record found.
Thanks,
*******************************************************************
Ernie Knox
IT Database Administrator Specialist
Sears Holdings - BU: I & T Group
3333 Beverly Rd., B4-266A
Hoffman Estates, IL. 60179
Office: (847) 286-5735
Email: Ernest.Knox@searshc.com
Blackberry: 2244650553@messaging.sprintpcs.com
<mailto:2244650553@messaging.sprintpcs.com>
Page via Skytel: 2244650553@sprint.skytel.com
<mailto:2244650553@sprint.skytel.com>
Informix or MySQL Primary: 9110210@skytel.com
<mailto:9110210@skytel.com>
Informix or MySQL Secondary: 7276872@skytel.com
<mailto:7276872@skytel.com>
" Yes we can make a Change! "
" It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS!
"
" Lets not forget - GO Pistons and Red Wings! "
GSU
*******************************************************************
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
_____
avast! Antivirus <http://www.avast.com>; : Outbound message clean.
Virus Database (VPS): 100814-1, 08/14/2010
Tested on: 8/14/2010 6:32:08 PM
avast! - copyright (c) 1988-2010 ALWIL Software.
I understand. I'll do that.
Thanks,
*******************************************************************
Ernie Knox
IT Database Administrator Specialist
Sears Holdings - BU: I & T Group
3333 Beverly Rd., B4-266A
Hoffman Estates, IL. 60179
Office: (847) 286-5735
Email: Ernest.Knox@searshc.com
Blackberry: 2244650553@messaging.sprintpcs.com
Page via Skytel: 2244650553@sprint.skytel.com
Informix or MySQL Primary: 9110210@skytel.com
Informix or MySQL Secondary: 7276872@skytel.com
" Yes we can make a Change! "
" It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS!
"
" Lets not forget - GO Pistons and Red Wings! "
GSU
*******************************************************************
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Paul Watson
Sent: Saturday, August 14, 2010 6:32 PM
To: ids@iiug.org
Subject: RE: 525: Failure to satisfy referential constr.... [20925]
Use violation tables ?
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Knox,
Ernest
Sent: Saturday, August 14, 2010 5:56 PM
To: ids@iiug.org
Subject: 525: Failure to satisfy referential constraint.... [20924]
Is there an easy quick way to determine the offending row(s) in the
constraint error?
alter table "dbascv1".scv_asn_detail add constraint (foreign
key (asn_key) references "dbascv1".scv_asn_header on delete
cascade constraint "dbascv1".scv_asn_detail_fk01);
525: Failure to satisfy referential constraint scv_asn_detail_fk01.
111: ISAM error: no record found.
Thanks,
*******************************************************************
Ernie Knox
IT Database Administrator Specialist
Sears Holdings - BU: I & T Group
3333 Beverly Rd., B4-266A
Hoffman Estates, IL. 60179
Office: (847) 286-5735
Email: Ernest.Knox@searshc.com
Blackberry: 2244650553@messaging.sprintpcs.com
<mailto:2244650553@messaging.sprintpcs.com>
Page via Skytel: 2244650553@sprint.skytel.com
<mailto:2244650553@sprint.skytel.com>
Informix or MySQL Primary: 9110210@skytel.com
<mailto:9110210@skytel.com>
Informix or MySQL Secondary: 7276872@skytel.com
<mailto:7276872@skytel.com>
" Yes we can make a Change! "
" It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS!
"
" Lets not forget - GO Pistons and Red Wings! "
GSU
*******************************************************************
************************************************************************
****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
_____
avast! Antivirus <http://www.avast.com>; : Outbound message clean.
Virus Database (VPS): 100814-1, 08/14/2010
Tested on: 8/14/2010 6:32:08 PM
avast! - copyright (c) 1988-2010 ALWIL Software.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
select * from scv_asn_detail where asn_key not in
(select asn_key from scv_asn_header);
Here's a nasty bit of SQL we use that'll list your referential constraints to
6 levels (and tell you if they're enabled or not) if that's any use:
select unique
trim(l1.tabname) l1,
s2.state|| " "||trim(l2.tabname) l2,
s3.state|| " "||trim(l3.tabname) l3,
s4.state|| " "||trim(l4.tabname) l4,
s5.state|| " "||trim(l5.tabname) l5,s6.state|| " "||trim(l6.tabname) l6
from
systables l1, sysconstraints c12, sysreferences r12,
systables l2, sysobjstate s2, outer (sysconstraints c23, sysreferences r23,
systables l3, sysobjstate s3, outer (sysconstraints c34, sysreferences r34,
systables l4, sysobjstate s4, outer (sysconstraints c45, sysreferences r45,
systables l5, sysobjstate s5, outer (sysconstraints c56, sysreferences r56,
systables l6, sysobjstate s6))))
where
l2.tabid = c12.tabid and c12.constrid = r12.constrid and l1.tabid = r12.ptabid
and l2.tabid = s2.tabid
and
l3.tabid = c23.tabid and c23.constrid = r23.constrid and l2.tabid = r23.ptabid
and l3.tabid = s3.tabid
and
l4.tabid = c34.tabid and c34.constrid = r34.constrid and l3.tabid = r34.ptabid
and l4.tabid = s4.tabid
and
l5.tabid = c45.tabid and c45.constrid = r45.constrid and l4.tabid = r45.ptabid
and l5.tabid = s5.tabid
and
l6.tabid = c56.tabid and c56.constrid = r56.constrid and l5.tabid = r56.ptabid
and l6.tabid = s6.tabid
order by 1,2,3
Well, the iiug posting tool messed up the formatting - the single spaces between the pipes in the trim statements at the top should have been 1,2,3,4 and 5 spaces each...