repair and scan FK's

[blom0344]

Apologies in advance, cause I'm probably asking a bit much

The challenge:

writing a scan an repair procedure (in one or more steps) which should:

1. Scan a database (DWH-type) for FK's of the nullable kind
2. Process this information to perform succesive updates to a certain default for the null values encountered.

for simplicities sake, I'll assume that the default already exists as a PK in the referenced table.
(Nice additional step)

Complications may be the fact that a table can have multiple FK's and that an FK may consist of more than 1 field.

So far I came up with scan scripts like:

-- nullable FK and index --
select
T.CONSTRAINT_NAME,
T.TABLE_NAME,
S.COLUMN_NAME,
S.POSITION,
V.NULLABLE,
V.DATA_DEFAULT,
V.DATA_TYPE,V.DATA_LENGTH,V.DATA_PRECISION,
(CASE WHEN X.INDEX_NAME IS NOT NULL THEN 'INDEXED' ELSE 'NOT INDEXED' END) AS IDX_IND from
sys.all_constraints T,
SYS.all_CONS_COLUMNS S,
SYS.all_TAB_COLUMNS V,
SYS.all_IND_COLUMNS X
where
T.OWNER = S.OWNER AND T.TABLE_NAME = S.TABLE_NAME AND T.CONSTRAINT_NAME = S.CONSTRAINT_NAME AND
S.OWNER = V.OWNER AND S.TABLE_NAME = V.TABLE_NAME AND S.COLUMN_NAME = V.COLUMN_NAME AND 
S.OWNER = X.TABLE_OWNER(+) AND S.TABLE_NAME = X.TABLE_NAME(+) AND S.COLUMN_NAME = X.COLUMN_NAME(+) AND 
t.constraint_type = 'R' 
and T.status = 'ENABLED' 
AND T.TABLE_NAME LIKE 'TB_%' 
AND V.NULLABLE = 'Y' 
AND T.OWNER = [COLOR=red]<user>[/color]
order by 2,1,3,4

and:

-- tables without FK ,but potential candidate for dummy PK

SELECT T.TABLE_NAME,S.CONSTRAINT_NAME,
FROM SYS.ALL_TABLES T,SYS.all_CONS_COLUMNS S,SYS.ALL_CONSTRAINTS X 
WHERE T.OWNER = S.OWNER AND T.TABLE_NAME = S.TABLE_NAME AND
T.OWNER = X.OWNER AND T.TABLE_NAME = X.TABLE_NAME AND
T.OWNER = [COLOR=red]<USER>[/color] AND T.TABLE_NAME LIKE 'TB%' AND X.CONSTRAINT_TYPE = 'P' AND
S.CONSTRAINT_NAME NOT LIKE 'SYS%' AND X.STATUS = 'ENABLED' AND
T.TABLE_NAME NOT IN 
(SELECT V.TABLE_NAME FROM SYS.ALL_CONSTRAINTS V WHERE V.OWNER = [COLOR=red]<USER>[/color] AND V.CONSTRAINT_TYPE = 'R')

Unfortunately, I am not very experienced with writing PL/SQL procedures to tackle this one.
Can imagine that someone , somewhere has had the same challenge.
Any input is highly appreciated...


Ties Blom

 

[SantaMufasa]

Ties,

I don't have a lot of time at the moment to go over your needs on this request, but judging by the data-dictionary views that you are accessing in your code, the results are likely similar to those from my favourite "Super Describe" script ("stru.sql" named for the word "structure") that shows not only the typical information that you would see on a SQL*Plus DESCRIBE <table> statement, but the script also shows ALL constraints that reside on each column (e.g. "PK": Primary Key, "FK": Foreign Key, "UK": Unique constraints, and "CK"" for Check constraints including NOT NULL), the names of the constraints (and indexes that enforce uniques of UK and PK constraints), the specific "lay-man's explanation" of what each constraint is enforcing, and for each constraint whether or not Oracle is currently enforcing the constraint.

Such output should make it very easy for you not only to identify Foreign Keys for each table, but since each FK also shows the table and column to which they refer, it will make if very easy to go to the parent table for sample default PK/FK values to match.

Before running the "stru.sql" script on any table, there is a one-time-only script named "strumake.sql", which makes a couple of infrastructure items for the "stru" script. Here are the contents of the one-time-only "strumake.sql" script:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
drop table temp_cons;
create table temp_cons as
select constraint_name, constraint_type, ' ' search_condition,
r_constraint_name, status
from user_constraints
where 1 = 2;
alter table temp_cons modify search_condition varchar(4000)
/
create or replace procedure prep_user_constraints (table_2_do in varchar)
is
   my_search_condition varchar(32767);
begin
   delete temp_cons;
   commit;
   for i in (select constraint_name
                   ,constraint_type
                   ,search_condition
                   ,r_constraint_name
                   ,status
                       from user_constraints
                      where table_name = upper(table_2_do)) loop
           my_search_condition := i.search_condition;
           insert into temp_cons values
                   (i.constraint_name
                   ,i.constraint_type
                   ,my_search_condition
                   ,i.r_constraint_name
                   ,i.status);
           commit;
   end loop;
end;
/

Next are the contents of the "stru.sql" script that you run each time you wish to see the structure of a table. (Also note that I did not use PL/SQL for the "stru.sql" script...just SQL SELECT statements.):

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any
REM industrial application or purpose nor is there any claim that
REM this or any similarly-distributed scripts are error free or
REM should be used for any purpose other than illustration.
REM **************************************************************
set echo off
set verify off
set feedback off
set linesize 100
set pagesize 500
break on "#" on a on b
accept x prompt "Enter table name: "
col a format a18 heading "Column|Name" word_wrapped
col b format a13 heading "Data Type|and Length"
col c format a43 heading "[Constraint Type: Name: En-/Dis-abled]|and Enforcement" word_wrapped
col "#" format 99 heading "Col| # "
exec prep_user_constraints('&x')
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
       '[' ||
           decode(con.constraint_type,'P','PK','R','FK','C','CK','U','UK')
           || ':' ||con.constraint_name
           || ':' ||con.status
           || '] ' ||
           decode(con.constraint_type,
              'P','NOT NULL/UNIQUE',
              'U','UNIQUE',
              'C',con.search_condition,
              'R','Match : '||r_constraint_name) c
from   temp_cons con,
       user_cons_columns con_col,
       user_tab_columns col
where  col.table_name      = upper('&x')
  and  con.constraint_name = con_col.constraint_name
  and  col.table_name      = con_col.table_name
  and  col.column_name     = con_col.column_name
  )
union
((select column_id "#",
         col.column_name a,
         col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col
 where col.table_name = upper('&x'))
minus
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col, user_cons_columns con_col
 where col.table_name  = upper('&x')
   and col.table_name  = con_col.table_name
   and col.column_name = con_col.column_name)
)
order by 1
/
accept again prompt "Another table (Y/N) ? "
set heading off
set pagesize 0
spool temp.sql
set termout off
select decode(upper('&again'),'Y','@stru') from dual;
set termout on
spool off
set heading on
set pagesize 23
set feedback on
@temp

So, a sample invocation of "stru" (following the one-time run of "strumake.sql") is:

SQL> @stru
Enter table name: s_emp

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER        [CK:S_EMP_ID_NN:ENABLED] "ID" IS NOT NULL
                                     [PK:S_EMP_ID_PK:ENABLED] NOT NULL/UNIQUE
  2 LAST_NAME          VARCHAR2(25)  [CK:S_EMP_LAST_NAME_NN:ENABLED] "LAST_NAME"
                                     IS NOT NULL

  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)   [UK:S_EMP_USERID_UK:ENABLED] UNIQUE
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER        [FK:S_EMP_MANAGER_ID_FK:ENABLED] Match :
                                     S_EMP_ID_PK

  8 TITLE              VARCHAR2(25)  [FK:S_EMP_TITLE_FK:ENABLED] Match :
                                     S_TITLE_TITLE_PK

  9 DEPT_ID            NUMBER        [FK:S_EMP_DEPT_ID_FK:ENABLED] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER
 11 COMMISSION_PCT     NUMBER        [CK:S_EMP_COMMISSION_PCT_CK:ENABLED]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)

Another table (Y/N) ?
********************************************************************************

Let us know if you find this useful for your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[blom0344]

Yes, actually a bit overwhelming
Though I would never be able to come up with this by my own, I could try to modify it to our needs.
We need only the information on the FK's and the reference to the PK (and corresponding table)
Next step would be to write the output to a table instead of to the screen (which would be something for a PL/SQL procedure I guess)

Anyway, thanks for the input. Much appreciated!

Ties Blom