Re: DBACCESS Problem

On Mon, 27 Jul 1998, Sadagopan, Sathish wrote: > (1) In a particular table the PK is referenced as a FK by many other > tables. So , in dbaccess menu options i navigate as follows > Table->Info->Constraints->Reference->Referenced Now, the output comes > up and so does the message "Duplicate value for a record with unique > key" Why does this message come up ? You don't say which platform or which version, so we can't easily comment on this. You could try either using SET EXPLAIN ON or the SQLIDEBUG and sqliprint tools -- the sqliprint program comes with 7.30, but not earlier versions. This would reveal what the DB-Access program is asking the database to do, and that in turn might reveal the trouble. Also, have you run oncheck or tbcheck against the database recently, to ensure it is all intact? > (2) Also I am not able to delete a row in my parent table; I get the > message: > "692: Key value for constraint (informix.pk_ab_defg) is still being > referenced" > I have checked in all the tables/columns output from (1) above and > found no referencing child rows for that key value. Why is this > happening? Is it that (1) is showing an incomplete output because of > the error message? It could very easily be because of that. You can run a single moderately complex SQL statement to list all the references in the database: SELECT C1.constrname refng_constr_name, C1.owner refng_constr_owner, C1.idxname refng_index_name, T1.tabid refng_table_tabid, T1.owner refng_table_owner, T1.tabname refng_table_name, C2.constrname refed_constr_name, C2.owner refed_constr_owner, C2.idxname refed_index_name, T2.tabid refed_table_tabid, T2.owner refed_table_owner, T2.tabname refed_table_name FROM 'informix'.SysReferences R, 'informix'.SysConstraints C1, 'informix'.SysConstraints C2, 'informix'.SysTables T2, 'informix'.SysTables T1 WHERE C1.constrtype = 'R' AND C1.tabid = T1.tabid AND C1.constrid = R.constrid AND R.ptabid = T2.tabid AND R.primary = C2.constrid You can add to the WHERE clause to specify the referenced table, using (for example): AND T2.Tabname = 'problem_table' This will list only the keys referencing the problem_table (provided you aren't in a MODE ANSI database with multiple tables with the same name and different owners). Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.59 -- see http://www.perl.com/CPAN