Create automatic synthetic primary key with sequence

Question

I have one table with a VARCHAR primary key that consists in multiple columns. One of these columns is a VARCHAR that has a maximum of 100 different values.

I want to generate a new table changing this specific VARCHAR column of the PK to an Integer using a sequence, the problem is the sequence I'm using generates a different Id for each row, it doesn't group the different existing Ids.

CREATE SEQUENCE my_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1

How can I make this sequence so it groups the values of the key that are the same on different rows?

Answer 1

You should do this in your test env. first and then only very very carefully when you are 100% certain do it on other systems. we dont have structure or data of your system so you need to fit this general solution to your needs we assume no liability :) .

SQL> create table old_table (object_name varchar2(30), object_type varchar2(19), val_1 varchar2(20), val_2 varchar2(20), 
     primary key(object_name, object_type));

Table created.                                                                                                                                                         

SQL> desc old_table                                                                                                                                                    
 Name                                      Null?    Type                                                                                                               
 ----------------------------------------- -------- ----------------------------                                                                                       
 OBJECT_NAME                               NOT NULL VARCHAR2(30)                                                                                                       
 OBJECT_TYPE                               NOT NULL VARCHAR2(19)                                                                                                       
 VAL_1                                              VARCHAR2(20)                                                                                                       
 VAL_2                                              VARCHAR2(20)                                                                                                       

 -- you can see this table has your table with composit pk , consists of (object_name and object_type) -- like your table.

 SQL> l                                                                                                                                                                 
  1  insert into old_table                                                                                                                                             
  2  select object_name, object_type, status, timestamp                                                                                                                
  3* from all_objects                                                                                                                                                  
SQL> /                                                                                                                                                                 

7289 rows created.                             
-- we just created some test data --similar to your table data

SQL> l                                                                                                                                                                 
  1  select object_type,count(*)                                                                                                                                       
  2  from old_table                                                                                                                                                    
  3* group by object_type                                                                                                                                              
SQL> /                                                                                                                                                                 

OBJECT_TYPE           COUNT(*)                                                                                                                                         
------------------- ----------                                                                                                                                         
CONSUMER GROUP               2                                                                                                                                         
EDITION                      1                                                                                                                                         
SCHEDULE                     3                                                                                                                                         
SEQUENCE                    13                                                                                                                                         
OPERATOR                    45                                                                                                                                         
PROCEDURE                   31                                                                                                                                         
WINDOW                       9                                                                                                                                         
SCHEDULER GROUP              4                                                                                                                                         
DESTINATION                  2                                                                                                                                         
PACKAGE                    296                                                                                                                                         
PROGRAM                     11                                                                                                                                         
XML SCHEMA                  31                                                                                                                                         
TRIGGER                      2                                                                                                                                         
JOB CLASS                    2                                                                                                                                         
SYNONYM                   3974                                                                                                                                         
VIEW                      1579                                                                                                                                         
TABLE                       96                                                                                                                                         
FUNCTION                   163                                                                                                                                         
INDEXTYPE                    8                                                                                                                                         
INDEX                       21                                                                                                                                         
TYPE                       995                                                                                                                                         
EVALUATION CONTEXT           1                                                                                                                                         

22 rows selected.                                         

-- here you can see total rows are 7289 but unique object_types( part of composit pk ) is only 22 distinct values..

SQL> CREATE TABLE NEW_TABLE1(ID NUMBER PRIMARY KEY, OBJECT_TYPE VARCHAR2(19));                                                                                         

Table created.                                                                                                                                                         

SQL> DESC NEW_TABLE1                                                                                                                                                   
 Name                                      Null?    Type                                                                                                               
 ----------------------------------------- -------- ----------------------------                                                                                       
 ID                                        NOT NULL NUMBER                                                                                                             
 OBJECT_TYPE                                        VARCHAR2(19)                             

 SQL> 

 -- here we created a new table with --id number-- as pk, and your obejct_type as value


CREATE SEQUENCE MY_SEQ MINVALUE 1 START WITH 1 INCREMENT BY 1; 

SQL> INSERT INTO NEW_TABLE1                                                                                                                                            
  2  SELECT MY_SEQ.NEXTVAL, OBJECT_TYPE FROM (SELECT DISTINCT OBJECT_TYPE FROM OLD_TABLE);                                                                             

22 rows created.                                                               

SQL> SELECT * FROM NEW_TABLE1;                                                                                                                                         

        ID OBJECT_TYPE                                                                                                                                                 
---------- -------------------                                                                                                                                         
         1 CONSUMER GROUP                                                                                                                                              
         2 EDITION                                                                                                                                                     
         3 SCHEDULE                                                                                                                                                    
         4 SEQUENCE                                                                                                                                                    
         5 OPERATOR                                                                                                                                                    
         6 PROCEDURE                                                                                                                                                   
         7 WINDOW                                                                                                                                                      
         8 SCHEDULER GROUP                                                                                                                                             
         9 DESTINATION                                                                                                                                                 
        10 PACKAGE                                                                                                                                                     
        11 PROGRAM                                                                                                                                                     
        12 XML SCHEMA                                                                                                                                                  
        13 TRIGGER                                                                                                                                                     
        14 JOB CLASS                                                                                                                                                   
        15 SYNONYM                                                                                                                                                     
        16 VIEW                                                                                                                                                        
        17 TABLE                                                                                                                                                       
        18 FUNCTION                                                                                                                                                    
        19 INDEXTYPE                                                                                                                                                   
        20 INDEX                                                                                                                                                       
        21 TYPE                                                                                                                                                        
        22 EVALUATION CONTEXT                                                                                                                                          

        22 rows selected.                               

    -- now we add new column to old/existing table

alter table old_table add (new_number_pk number);   

-- update the new column with id/number data

 SQL> update old_table set new_number_pk = ( select id from new_table1 where object_type = old_table.object_type);

  7289 rows updated.

SQL> select * from old_table where rownum <  20 order by object_type ;


OBJECT_NAME                    OBJECT_TYPE         VAL_1                VAL_2                NEW_NUMBER_PK  
------------------------------ ------------------- -------------------- -------------------- -------------  
V$FLASHBACK_DATABASE_STAT      SYNONYM             VALID                2011-08-28:22:11:07             15  
V$PARAMETER                    SYNONYM             VALID                2011-08-28:22:11:07             15  
V$RESTORE_POINT                SYNONYM             VALID                2011-08-28:22:11:07             15  
V$ROLLNAME                     SYNONYM             VALID                2011-08-28:22:11:07             15  
V$ROLLSTAT                     SYNONYM             VALID                2011-08-28:22:11:07             15  
V$UNDOSTAT                     SYNONYM             VALID                2011-08-28:22:11:07             15  
V$SGA                          SYNONYM             VALID                2011-08-28:22:11:07             15  
V$CLUSTER_INTERCONNECTS        SYNONYM             VALID                2011-08-28:22:11:07             15  
V$CONFIGURED_INTERCONNECTS     SYNONYM             VALID                2011-08-28:22:11:07             15  
V$ROWCACHE_SUBORDINATE         SYNONYM             VALID                2011-08-28:22:11:07             15  
V$PARAMETER2                   SYNONYM             VALID                2011-08-28:22:11:07             15  
V$OBSOLETE_PARAMETER           SYNONYM             VALID                2011-08-28:22:11:07             15  
V$SYSTEM_PARAMETER             SYNONYM             VALID                2011-08-28:22:11:07             15  
V$SYSTEM_PARAMETER2            SYNONYM             VALID                2011-08-28:22:11:07             15  
V$SPPARAMETER                  SYNONYM             VALID                2011-08-28:22:11:07             15  
V$PARAMETER_VALID_VALUES       SYNONYM             VALID                2011-08-28:22:11:07             15  
V$ROWCACHE                     SYNONYM             VALID                2011-08-28:22:11:07             15  
V$ROWCACHE_PARENT              SYNONYM             VALID                2011-08-28:22:11:07             15  
V_$RESTORE_POINT               VIEW                VALID                2011-08-28:22:11:07             16  

19 rows selected.                                                                                       
-- drop old primary key
SQL> alter table old_table drop primary key;

Table altered.   

-- mark object_type column unused in old_table
SQL> alter table old_table set unused  column object_type;

Table altered.

-- crate new pk with new column on old_table
SQL> alter table old_table                                           
  2  add constraint pk_old_table primary key(object_name, new_number_pk);

Table altered.


-- drop unused column
SQL> alter table old_table drop unused columns;  

Table altered. 

Answer 2

step 1 - create a new table with all the old PK columns and a new sequence driven one.

step 2 insert into the new table as select distinct columns from the old table

step 3 add a new PK column to the original table.

step 4 update original table with the new PK from the new table where the columns match