Failed Referential Integrity

Posted in 1999

Topics: Triggers, Constraints & Referential Integrity, Platform-Specific Issues

Richard Auslander — Wed, 30 Jun 1999 14:13:31 -0700 — source: Informix-list mailing list archive (1991-1998)

We are using Dynamic Server, Version 7.30UC5-1, on Solaris. I'm having a problem identifying which record caused a referential integrity failure when inserting a large number (a few million) of records into a child table. The error messages refuse under any circumstances to identify *which* key value failed to match. I'm very familiar with the queries I can perform to identify the failing record in the source data, but they're intensely slow and unmanageable with these large data volumes. Does anyone know a trick or feature which might help? Many thanks. Rich -- Richard C. Auslander Database Manager AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com

↪ replying to Richard Auslander

Paul Brown — 30 Jun 1999 21:42:17 GMT — source: Informix-list mailing list archive (1991-1998)

Richard Auslander (rich@airflash.com) wrote: : We are using Dynamic Server, Version 7.30UC5-1, on Solaris. I'm having : a problem identifying which record caused a referential integrity : failure when inserting a large number (a few million) of records into a : child table. The error messages refuse under any circumstances to : identify *which* key value failed to match. I'm very familiar with the : queries I can perform to identify the failing record in the source data, : but they're intensely slow and unmanageable with these large data : volumes. Does anyone know a trick or feature which might help? Many : thanks. Well, not exactly a trick, but have a look at the SET CONSTRAINTS command. Basically, it lets you specify what action the engine should take when a CONSTRAINT (referential key in this case) is violated. What you want is 'FILTERING'. This means that the server places information about the nature of the violation into a special table called the diagnostics table. This should let you figure out what the 'problem row' is. Hope this helps! -- ===================================================================== Paul Brown ^..^ pbrown@postgres.Berkeley.EDU (oo) - Oink! #include <std_disclaimer.h> "Think global - act loco!" - Zippy the Pinhead =====================================================================