467,887 Members | 1,574 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,887 developers. It's quick & easy.

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
Aug 16 '07 #1
  • viewed: 16933
Share:
1 Reply
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.

Similar topics

2 posts views Thread by Sunny | last post: by
5 posts views Thread by Patrick | last post: by
reply views Thread by cher | last post: by
reply views Thread by 7stud | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.