Tuesday, June 1, 2010

ORA-01460 when foreign key column on different scale

ORA-01460: unimplemented or unreasonable conversion requested will be thrown when you try to delete from parent table with foreign key column with "on delete cascade" on a deferent scale.

Test as below

create table x (a number, b varchar2(100 byte) primary key);
create table y (c varchar2(30 BYTE), d number,
foreign key(c) references x (b) on delete cascade);

Insert into X (A,B) values (2,'abcdefghijklmnopqrstuvwxyzabcdefg');
Insert into X (A,B) values (3,'abcdefghijklmnopqrstuvwxyzabcdefgi');
Insert into X (A,B) values (4,'abcdefghijklmnopqrstuvwxyz abcdf');
Insert into X (A,B) values (5,'abcdefghijklmnopqrstuvwxyz abcdfg');
Insert into X (A,B) values (6,'abcdefghijklmnopqrstuvwxyz abcdfgi');


Try
 delete from x where a = 3;

and
delete from x where a = 3
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
will be thrown on Oracle versions 10.2 and 11.1 but not in 11.2

On 11.2 the delete operation will suceed without an error.

To fix the problem both referred and referrenced columns should have the same scale
ie. both should be varchar2(100 byte) for this example. Once the change is made delete operation will work on both 10.2 and 11.1