1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Who is referencing this item (constraints)

Discussion in 'SQL PL/SQL' started by PapaGeek, Feb 6, 2013.

  1. PapaGeek

    PapaGeek Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Let’s say I sell a number of items, and the item table is referenced all over the database.

    I can find out who references the “ITEMS” table with the following SQL:

    SELECT table_name
    FROM ALL_CONSTRAINTS
    WHERE constraint_type = 'R' -- "Referential integrity"
    AND r_constraint_name IN (
    SELECT constraint_name
    FROM ALL_CONSTRAINTS
    WHERE table_name = ‘ITEM’
    AND constraint_type IN ('U', 'P') -- "Unique" or "Primary key"
    )

    My specific situation returns a list of over 100 TABLE_NAMEs

    I’m trying to find out all the references to a specific item in the entire database. I could create individual queries for each of the tables returned:

    SELECT * from user.SALES where ITEM_NO = 123:
    -- or --
    SELECT count(*) from user.SALES where ITEM_NO = 123:

    But that requires the creation of over 100 individual queries.

    Is there a way to feed the output of the first query into a second query to show me which tables are referencing a specific item?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No since you can't dynamically specify a table name in the FROM clause. You can generate dynamic sql with a PL/SQL block:

    Code (SQL):

    SQL> CREATE TABLE item(
      2          item_no NUMBER,
      3          item_desc varchar2(80),
      4          CONSTRAINT item_pk PRIMARY KEY (item_no));
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE item_sold(
      2          item_no NUMBER,
      3          item_qty NUMBER,
      4          item_sell_dt    DATE);
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE item_order(
      2          item_no NUMBER,
      3          item_qty NUMBER,
      4          item_ord_dt     DATE);
     
    TABLE created.
     
    SQL>
    SQL> ALTER TABLE item_sold ADD CONSTRAINT item_fk FOREIGN KEY (item_no) REFERENCES item;
     
    TABLE altered.
     
    SQL> ALTER TABLE item_order ADD CONSTRAINT item_ord_fk FOREIGN KEY (item_no) REFERENCES item;
     
    TABLE altered.
     
    SQL>
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..125 loop
      3                  INSERT INTO item
      4                  VALUES(i, 'Item '||i);
      5                  INSERT INTO item_sold
      6                  VALUES(MOD(i,126), i, sysdate);
      7                  INSERT INTO item_order
      8                  VALUES(MOD(i,126), MOD(i,11)+1, sysdate);
      9          END loop;
     10
     11          commit;
     12
     13  END;
     14  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> DECLARE
      2          sqlstmt varchar2(32767);
      3
      4          cursor get_ref_tabs IS
      5          SELECT TABLE_NAME
      6          FROM ALL_CONSTRAINTS
      7          WHERE constraint_type = 'R' -- "Referential integrity"
      8          AND r_constraint_name IN (
      9                  SELECT constraint_name
     10                  FROM ALL_CONSTRAINTS
     11                  WHERE TABLE_NAME = 'ITEM'
     12                  AND constraint_type IN ('U', 'P') -- "Unique" or "Primary key"
     13          );
     14
     15          rec_ct NUMBER;
     16
     17  BEGIN
     18          FOR tabrec IN get_ref_tabs loop
     19                  sqlstmt:='select count(*) from '||tabrec.TABLE_NAME||' where item_no = 123';
     20
     21                  EXECUTE immediate sqlstmt INTO rec_ct;
     22
     23                  dbms_output.put_line(tabrec.TABLE_NAME||' contains '||rec_ct||' referenced records.');
     24          END loop;
     25
     26  END;
     27  /
    ITEM_ORDER contains 1 referenced records.
    ITEM_SOLD contains 1 referenced records.
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
     
  3. PapaGeek

    PapaGeek Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thanks, looks like a good response, I'll give it a try in my environment.