sql error -530

Ist this possible to get the causing column for a sql error -530
(foreign key) in an insert command?

What I mean:

evaluate sqlcode
if -530
if causing_column = "Model"
....
end if
if causing_column = "Group"
....
end if
end if
end evaluate


Thanks for help!
Wojtek Kusch

Wojtek Kusch wrote:

> Ist this possible to get the causing column for a sql error -530
> (foreign key) in an insert command?
>
> What I mean:
>
> evaluate sqlcode
> if -530
> if causing_column = "Model"
> ....
> end if
> if causing_column = "Group"
> ....
> end if
> end if
> end evaluate

The message gives you the name of the constraint violated. So you could look
up in the DB2 catalog which column(s) this constraint is defined on. If
all your FKs are just on a single column, it works that way. If you have
multi-column FKs, then DB2 doesn't tell you the name of the specific column
or columns that failed - because you have to take all columns of the
constraint into consideration. For example if you have this:

CREATE TABLE p ( c1 INT NOT NULL, c2 INT NOT NULL, PRIMARY KEY(c1, c2) )@
INSERT INTO p VALUES (1, 2), (2, 1)@

C1 C2
--- ---
1 2
2 1

Now you want to insert into the referencing table C:

CREATE TABLE c ( c1 INT, c2 INT, FOREIGN KEY(c1, c2) REFERENCES p )@
INSERT INTO c VALUE (1, 1)@

This INSERT statement fails with -530 (SQL0530). But which column is at
fault - C1 or C2? So you have to look at all columns together.

Btw, the following query will return all columns in a foreign key:

SELECT colname
FROM syscat.keycoluse
WHERE ( tabschema, tabname ) = ( ..., ... ) AND
constname = '...'


--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany