Search

Top 60 Oracle Blogs

Recent comments

Virtual Integrity

A recent question on the Oracle-L list server described a problem with data coming in from SQL Server and an oddity with referential integrity failing on Oracle because (for example) a child row was in lower case while the parent was in upper.

This raised a few comments on how you might handle referential integrity while allowed case to differ. No doubt it’s been done before – by Tom Kyte if no-one else – but the first thought that crossed my mind was to use virtual columns:


drop table child;
drop table parent;

create table parent (
	v1		varchar2(10) not null,
	padding		varchar2(100),
	v_pk		generated always as ( upper(v1)) virtual
);

alter table parent add constraint par_pk primary key(v_pk);

create table child (
	v1		varchar2(10) not null,
	padding		varchar2(100),
	v_ref		generated always as ( upper(v1)) virtual
);

alter table child add constraint chi_ref_par foreign key (v_ref) references parent(v_pk);

insert into parent (v1, padding) values ('ab',rpad('x',100));

insert into child  (v1, padding) values ('ab',rpad('x',100));
insert into child  (v1, padding) values ('Ab',rpad('x',100));
insert into child  (v1, padding) values ('aB',rpad('x',100));
insert into child  (v1, padding) values ('AB',rpad('x',100));

commit;

Prompt Expect ORA-00001 here:

insert into parent (v1, padding) values ('AB',rpad('x',100));



Obviously there are several details you’d have to check in the application to make sure that this strategy didn’t have any nasty side effects – does any of the code do an insert without referencing a column list, for example – but at first sight this looks like one way of matching incoming case-insensitivity “transparently”. You might still have reasons for creating case-insensitive indexes on the original columns, though, so I can’t claim that the method is virtually free.