-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|