Hi,

I am trying to right a data import module for my application which uses Firebird.

Some of the tables have unique constraints so if an imported record matches the INSERT statement fails - which is fine. However I would like to know which record in the original table caused the conflict so I can offer the user the chance to overwrite (i.e. retry the INSERT as an UPDATE).

The problem is that I don't know which record caused the problem. Of course I can do a SELECT from the original table with the same values as the imported record, but I don't know the fields that are constrained.

I found this SQL:

SELECT rc.RDB$CONSTRAINT_NAME,
s.RDB$FIELD_NAME AS field_name
FROM RDB$INDEX_SEGMENTS s
LEFT JOIN RDB$INDICES i ON i.RDB$INDEX_NAME = s.RDB$INDEX_NAME
LEFT JOIN RDB$RELATION_CONSTRAINTS rc ON rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME
WHERE i.RDB$RELATION_NAME='TABLE1' -- table name
and rc.RDB$CONSTRAINT_NAME= 'UNQ1' -- constraint name
AND rc.RDB$CONSTRAINT_TYPE IS NOT NULL
ORDER BY s.RDB$FIELD_POSITION

...which should give me the field list, but it requires me to either know the relevant constraint name. I guess I could parse the error message, but that seems a little risky considering the error message could be changed in future versions (or language configurations)

Any ideas are most welcome!