Oracle Scratchpad

September 30, 2013

Virtual Integrity

Filed under: Indexing,Infrastructure,Oracle — Jonathan Lewis @ 3:59 pm BST Sep 30,2013

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.

2 Comments »

  1. Hi,

    I have worked on an exact similar implementation. This was a SQL Server 2008 R2 to Oracle 11gr2 migration. We wanted to use SQL Developer as a migration tool but as is migration wasn’t possible and we had to do a lot of manual work. Every Table, View, SP etc had to be changed because of this extra virtual column and the JOINS changed in every query. Triggers being the biggest villains. Lot’s of pain and huge effort.

    We could not change data because this was user login data therefore case mattered for unix systems but did not for Windows AD.

    In my opinion though, it’s a design flaw. Why make these columns PK, FK?

    Regards,
    Vikram R

    Comment by vikramrathourVikram — October 1, 2013 @ 3:16 pm BST Oct 1,2013 | Reply

    • Vikram,

      Thanks for supplying some more examples of the types of difficulty that you can run into when playing games like this.

      I’d certainly agree that allowing case-insensitive checks on foreign key constraints is a bad idea.

      Comment by Jonathan Lewis — October 1, 2013 @ 6:41 pm BST Oct 1,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,257 other followers