v6 Constraint

From: Oracle Schema Management <schema_at_cnw01.storesys.coles.oz.au>
Date: Fri, 31 Dec 1993 05:22:37 GMT
Message-ID: <1993Dec31.052237.1259_at_cnw01.storesys.coles.oz.au>

                                                Oracle Version: 6.0.36.5.1
    

    Hi fellow netters,     

          Our users have a shell script that restores a table from an     export file. The user enters the user & table name they wish to re-create.     The table is checked for foreign key constraints referencing the table they     wish to re-create. If a foreign key constraint is found, the constraint     is dropped and the table the user wishes to re-load can be dropped and     re-created. I have an sql procedure that generates the drop constraint     script. My problem is before I drop the constraint, I want to create     the sql script to put it back on after the table has been reloaded. I     know sqlplus has a feature called decode, but I am not familiar enough     with it to create the correct syntax to generate the the sql.     

    Here is an example of two tables and the sql script I use to drop the     constraints.     

    #######################################################################

    create table peter.plu
(

    plu_id            number(13)not null primary key CONSTRAINT pluid_pk,
    sell_itm_id       number(8) not null,
    itm_shrt_desc     char(12),
    vis_ver_ind       char(1) not null,
                foreign key (sell_itm_id)
                references peter.stor_sell_itm (sell_itm_id)
                CONSTRAINT selid_fk);
    

    create table peter.stor_sell_itm
(

     sell_itm_id number(8)not null primary key CONSTRAINT     itselid_pk,

     stor_sell_updt_dt                        date not null,
     itm_del_by_dt                            date,
     itm_size_qty                             char(5),
     itm_size_um                              char(5),
     sell_prc_amt                             number(8,2)not null,
     sell_prc_ty_cd                           char(2)not null);
    
    
    #######  Script to Drop Foreign Key constraints referencing ##########
    #######           the table you wish to drop.               ##########

    set heading off
    set feedback off
    set pagesize 0
    spool &table.sql
    select 'alter table '||b.table_name||' drop constraint     '||b.constraint_name||';'
    from all_constraints a, all_constraints b     where a.constraint_name=b.r_constraint_name     and a.table_name='&table'
    /
    spool off
    set feedback on
    start $table.lst          

    ########################### cut here #################################
    
     If I run the above script for table stor_sell_itm, it will generate:
    

    alter table PLU drop constraint SELID_FK;     

    I need to be able to generate an alter table create foreign key script     before I actually drop the constraint. for the above example of     stor_sell_itm it would generate a statement like this:     

    alter table plu
    add( FOREIGN KEY (sell_itm_id)

         references peter.stor_sell_itm(sell_itm_id)
         CONSTRAINT selid_fk

    );     

     
      Has anybody created an sql procedure to do this, if so would you mind 

    sharing it.     

    regards Peter.     

    Mail Address: schema_at_cnw01.storesys.coles.oz.au Received on Fri Dec 31 1993 - 06:22:37 CET