Table data type change constraints Problem!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Table data type change constraints Problem!

  1. #1
    Join Date
    Feb 2001
    Posts
    286
    Hi all!

    Query:

    1.I have a column STORE_NO number(2) being combined Primary key and

    Foreign Keys along with other columns in the Parent and Child

    Tables.Both primary and Foreign Keys constraints are defined for them!

    Now If I would like to change the data type of STORE_NO from number(2)

    to varchar2(4),how should we be able to do it considering the above
    constraints and the STORE_NO column contains datas therein!

    Tha table is as under:

    SQL> desc t_str_code_master
    Name Null? Type
    ------------------------------- -------- ----
    COMPANY_CODE NUMBER(2)
    STORE_DESC NOT NULL VARCHAR2(50)
    LOCATION VARCHAR2(50)
    STORE_NO NUMBER(2)

    SQL> alter table t_str_code_master
    2 modify store_no varchar2(4);
    modify store_no varchar2(4)
    *
    ERROR at line 2:
    ORA-02267: column type incompatible with referenced column type

    What and where should I look for to resolve the above error!


    Thanks&Regards,

    Amit.




  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Hi Amit,
    Sorry for misinterpreting your other thread which is the same to this.

    Anyway,
    I doubt if you can just disable the constraints, I believe you need to drop it and then recreate it after you have successfully recreate the table with the desired data type.

    Assume table orig_tab:
    field1 number(2)
    field2 varchar2(40)

    Table you wanted to be new_tab:
    field1 varchar2(4)
    field2 varchar2(40)


    Here's what I usually do:
    1. extract the script to recreate the constraints, then drop the constraints.
    2. create the table NEW_TAB with the desired data type.
    3. populate the NEW_TAB from ORIG_TAB table:
    insert into NEW_TAB select to_char(field1), field2 from ORIG_TAB;
    4. check if the same records counts for both table.
    5. drop the orig table.
    6. recreate the ORIG_TAB table by duplicating the NEW_TAB
    create table ORIG_TAB as select * from NEW_TAB;
    7. then, recreate the constraints.

    Hope this time it can help.

    rey :-))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width