{"id":145,"date":"2011-08-24T03:06:02","date_gmt":"2011-08-24T10:06:02","guid":{"rendered":"http:\/\/www.zulutown.com\/blog\/?p=145"},"modified":"2011-08-24T03:06:02","modified_gmt":"2011-08-24T10:06:02","slug":"finding-an-oracle-foreign-key-by-its-id-to-understand-which-tables-are-referenced-by-it","status":"publish","type":"post","link":"http:\/\/www.zulutown.com\/blog\/2011\/08\/24\/finding-an-oracle-foreign-key-by-its-id-to-understand-which-tables-are-referenced-by-it\/","title":{"rendered":"Finding an Oracle Foreign Key by its ID to understand which tables are referenced by it"},"content":{"rendered":"<p>It&#8217;s quite common to receive errors when trying to executed SQL insert and delete because some of the changes we&#8217;re executing, cause some <strong>Oracle foreign keys<\/strong> to be violated.<\/p>\n<p>I don&#8217;t like that the error message given by Oracle is not very clear about what&#8217;s exactly the <strong>cause of the problem<\/strong>, it just says something like this:<\/p>\n<pre>Error report:\r\nSQL Error: ORA-02292: integrity constraint (MYDB.FKBA1C59B51590B46E) violated - child record found\r\n02292. 00000 - \"integrity constraint (%s.%s) violated - child record found\"\r\n*Cause:    attempted to delete a parent key value that had a foreign\r\n           dependency.\r\n*Action:   delete dependencies first then parent or disable constraint.<\/pre>\n<p>In this message, I just know that the violated key name is\u00a0FKBA1C59B51590B46E, in this case I usually need to write some more SQL to update (or delete) also the table where there are some <strong>dependencies<\/strong>.<\/p>\n<p>Of course it&#8217;s useful to know <strong>which is that table<\/strong>. The command to execute is very simple, and I hardly understand why Oracle doesn&#8217;t show directly the table name where the foreign key is violated.<\/p>\n<p>To know more about that cryptic\u00a0FKBA1C59B51590B46E, just execute this:<\/p>\n<pre>select * from ALL_CONSTRAINTS where constraint_name = 'FKBA1C59B51590B46E';<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>How to understand in which table, there is the foreign key that is violated during some update\/delete commands <a href=\"http:\/\/www.zulutown.com\/blog\/2011\/08\/24\/finding-an-oracle-foreign-key-by-its-id-to-understand-which-tables-are-referenced-by-it\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[157,158,154,155,161,160,153,162,159,156],"_links":{"self":[{"href":"http:\/\/www.zulutown.com\/blog\/wp-json\/wp\/v2\/posts\/145"}],"collection":[{"href":"http:\/\/www.zulutown.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.zulutown.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.zulutown.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.zulutown.com\/blog\/wp-json\/wp\/v2\/comments?post=145"}],"version-history":[{"count":3,"href":"http:\/\/www.zulutown.com\/blog\/wp-json\/wp\/v2\/posts\/145\/revisions"}],"predecessor-version":[{"id":148,"href":"http:\/\/www.zulutown.com\/blog\/wp-json\/wp\/v2\/posts\/145\/revisions\/148"}],"wp:attachment":[{"href":"http:\/\/www.zulutown.com\/blog\/wp-json\/wp\/v2\/media?parent=145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.zulutown.com\/blog\/wp-json\/wp\/v2\/categories?post=145"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.zulutown.com\/blog\/wp-json\/wp\/v2\/tags?post=145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}