By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,702 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

sql error -530

P: n/a
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
Aug 16 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Aug 17 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.