{"id":129,"date":"2017-08-14T01:36:13","date_gmt":"2017-08-14T01:36:13","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=129"},"modified":"2017-08-14T01:36:13","modified_gmt":"2017-08-14T01:36:13","slug":"tables-with-no-foreign-keys","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/14\/tables-with-no-foreign-keys\/","title":{"rendered":"Tables With No foreign Keys"},"content":{"rendered":"\n<pre class=\"lang:default decode:true \" >SELECT table_name,\r\n       constraint_name,\r\n          cname1\r\n       || NVL2 (cname2, ',' || cname2, NULL)\r\n       || NVL2 (cname3, ',' || cname3, NULL)\r\n       || NVL2 (cname4, ',' || cname4, NULL)\r\n       || NVL2 (cname5, ',' || cname5, NULL)\r\n       || NVL2 (cname6, ',' || cname6, NULL)\r\n       || NVL2 (cname7, ',' || cname7, NULL)\r\n       || NVL2 (cname8, ',' || cname8, NULL)\r\n           columns\r\n  FROM (  SELECT b.table_name,\r\n                 b.constraint_name,\r\n                 MAX (DECODE (position, 1, column_name, NULL)) cname1,\r\n                 MAX (DECODE (position, 2, column_name, NULL)) cname2,\r\n                 MAX (DECODE (position, 3, column_name, NULL)) cname3,\r\n                 MAX (DECODE (position, 4, column_name, NULL)) cname4,\r\n                 MAX (DECODE (position, 5, column_name, NULL)) cname5,\r\n                 MAX (DECODE (position, 6, column_name, NULL)) cname6,\r\n                 MAX (DECODE (position, 7, column_name, NULL)) cname7,\r\n                 MAX (DECODE (position, 8, column_name, NULL)) cname8,\r\n                 COUNT (*) col_cnt\r\n            FROM (SELECT SUBSTR (table_name, 1, 30) table_name,\r\n                         SUBSTR (constraint_name, 1, 30) constraint_name,\r\n                         SUBSTR (column_name, 1, 30) column_name,\r\n                         position\r\n                    FROM user_cons_columns) a,\r\n                 user_constraints b\r\n           WHERE     a.constraint_name = b.constraint_name\r\n                 AND b.constraint_type = 'R'\r\n        GROUP BY b.table_name, b.constraint_name) cons\r\n WHERE col_cnt &gt; ALL (  SELECT COUNT (*)\r\n                          FROM user_ind_columns i\r\n                         WHERE     i.table_name = cons.table_name\r\n                               AND i.column_name IN (cname1,\r\n                                                     cname2,\r\n                                                     cname3,\r\n                                                     cname4,\r\n                                                     cname5,\r\n                                                     cname6,\r\n                                                     cname7,\r\n                                                     cname8)\r\n                               AND i.column_position &lt;= cons.col_cnt\r\n                      GROUP BY i.index_name);<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>SELECT table_name, constraint_name, cname1 || NVL2 (cname2, &#8216;,&#8217; || cname2, NULL) || NVL2 (cname3, &#8216;,&#8217; || cname3, NULL) || NVL2 (cname4, &#8216;,&#8217; || cname4, NULL) || NVL2 (cname5, &#8216;,&#8217; || cname5, NULL) || NVL2 (cname6, &#8216;,&#8217; || cname6, NULL) || NVL2 (cname7, &#8216;,&#8217; || cname7, NULL) || NVL2 (cname8, &#8216;,&#8217; || cname8, NULL) columns FROM &#8230; <a title=\"Tables With No foreign Keys\" class=\"read-more\" href=\"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/14\/tables-with-no-foreign-keys\/\" aria-label=\"Read more about Tables With No foreign Keys\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[32],"class_list":["post-129","post","type-post","status-publish","format-standard","hentry","category-oracle-database","tag-foreign-keys"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/129","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=129"}],"version-history":[{"count":1,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/129\/revisions"}],"predecessor-version":[{"id":130,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/129\/revisions\/130"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=129"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}