Re: constraints and foreign keys

From: Michael J. Hillanbrand II <mjhii_at_pop.erols.com>
Date: 1996/09/05
Message-ID: <322EFCF0.4FC1_at_pop.erols.com>

rem
rem Copyright (c) PirateWare - All Rights Reserved. rem
rem File: GCNSTR2L.SQL
rem Will Create a table denormalizing user cons columns. rem The column name field will be concatenated where rem group by constraint name, table name. The final result will rem be a horizontal list of column names for each group. This rem script uses Oracle's current maximum # of columns in an rem index as the basis for the sequence maxalue, if Oracle rem allows for more columns increase the maxvaule on the rem sequence accordingly.
rem
rem
rem Revision History:

rem Date:			Name:			Comment:
rem 28FEB95			mjhii			Creation.

rem
rem

drop table SUB CONS COLUMNS
/

CREATE TABLE SUB CONS COLUMNS
( owner varchar2(30)
, constraint name varchar2(30)
, table name varchar2(30)
, column1 varchar2(30)
, column2 varchar2(30)
, column3 varchar2(30)
, column4 varchar2(30)
, column5 varchar2(30)
, column6 varchar2(30)
, column7 varchar2(30)
, column8 varchar2(30)
, column9 varchar2(30)
, column10 varchar2(30)
, column11 varchar2(30)
, column12 varchar2(30)
, column13 varchar2(30)
, column14 varchar2(30)
, column15 varchar2(30)
, column16 varchar2(30)

)
/

drop table USUB CONS COLUMNS
/  

CREATE TABLE USUB CONS COLUMNS
( owner varchar2(30)
, constraint name varchar2(30)
, table name varchar2(30)
, column1 varchar2(30)
, column2 varchar2(30)
, column3 varchar2(30)
, column4 varchar2(30)
, column5 varchar2(30)
, column6 varchar2(30)
, column7 varchar2(30)
, column8 varchar2(30)
, column9 varchar2(30)
, column10 varchar2(30)
, column11 varchar2(30)
, column12 varchar2(30)
, column13 varchar2(30)
, column14 varchar2(30)
, column15 varchar2(30)
, column16 varchar2(30)

)
/

declare
  cursor c2 is
    select
      owner, constraint name, table name, column name, position     from
      all cons columns
    where

      constraint name in
        (select constraint name from all cons columns where position > 1)

    order by position;

  cursor c3 is
    select
      owner, constraint name, table name, column name, position     from
      user cons columns
    where

      constraint name in
        (select constraint name from user cons columns where position > 1)

    order by position;

ws owner					all cons columns.owner%type;
ws constraint name	all cons columns.constraint name%type;
ws table name			all cons columns.table name%type;
ws column name			all cons columns.column name%type;
ws position				all cons columns.position%type;

wsu owner             user cons columns.owner%type;
wsu constraint name   user cons columns.constraint name%type;
wsu table name        user cons columns.table name%type;
wsu column name       user cons columns.column name%type;
wsu position          user cons columns.position%type;

begin
  open c2;
    loop

      fetch c2 into 
        ws owner, ws constraint name, ws table name, ws column name, ws position;

    If (c2%found) then
      If (ws position  1) then
        insert into sub cons columns
          (owner, constraint name, table name, column1)
        values
          (ws owner, ws constraint name, ws table name, ws column name);
      Elsif (ws position  2) then
        update sub cons columns
          set column2  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  3) then
        update sub cons columns
          set column3  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  4) then
        update sub cons columns
          set column4  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  5) then
        update sub cons columns
          set column5  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  6) then
        update sub cons columns
          set column6  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  7) then
        update sub cons columns
          set column7  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  8) then
        update sub cons columns
          set column8  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  9) then
        update sub cons columns
          set column9  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  10) then
        update sub cons columns
          set column10  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  11) then
        update sub cons columns
          set column11  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  12) then
        update sub cons columns
          set column12  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  13) then
        update sub cons columns
          set column13  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  14) then
        update sub cons columns
          set column14  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  15) then
        update sub cons columns
          set column15  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      Elsif (ws position  16) then
        update sub cons columns
          set column16  ws column name
        where
          constraint name  ws constraint name and table name  ws table name;
      End if;

    else exit;
    end if;
  end loop;
  commit;
close c2;

  open c3;
    loop

      fetch c3 into
        wsu owner, wsu constraint name, wsu table name, 
        wsu column name, wsu position;
 
    If (c3%found) then
      If (wsu position  1) then
        insert into usub cons columns
          (owner, constraint name, table name, column1)
        values
          (wsu owner, wsu constraint name, wsu table name, wsu column name);
      Elsif (wsu position  2) then
        update usub cons columns
          set column2  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  3) then
        update usub cons columns
          set column3  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  4) then
        update usub cons columns
          set column4  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  5) then
        update usub cons columns
          set column5  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  6) then
        update usub cons columns
          set column6  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  7) then
        update usub cons columns
          set column7  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  8) then
        update usub cons columns
          set column8  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  9) then
        update usub cons columns
          set column9  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  10) then
        update usub cons columns
          set column10  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  11) then
        update usub cons columns
          set column11  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  12) then
        update usub cons columns
          set column12  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  13) then
        update usub cons columns
          set column13  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  14) then
        update usub cons columns
          set column14  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  15) then
        update usub cons columns
          set column15  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      Elsif (wsu position  16) then
        update usub cons columns
          set column16  wsu column name
        where
          constraint name  wsu constraint name and table name  wsu table name;
      End if;

    else exit;
    end if;
  end loop;
  commit;
close c3;

end;
/

--

Received on Thu Sep 05 1996 - 00:00:00 CEST