Skip to Main Content
  • Questions

  • Possibility of functional index/key in foreign key

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Marián.

Asked: January 12, 2018 - 12:34 pm UTC

Last updated: January 18, 2018 - 5:58 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi,
I want to ask if there is possibility to use functional index/key in foreign key. As an example :
In T1 there is opera_user_id column, with index upper(opera_user_id), in T2 a column ID, indexed as PK. From T1 there is FK defined by the column opera_user_id. I've found that not all of opera_user_id values are commited as upper case, so I would need now FK expression upper(opera_user_id). Is it possible?
Anyway, I guess that the opera_user_id in T1 can't use the existing functional index in FK and therefore problems could raise.

Thank you
Maros

and Chris said...

Not exactly...

You can't include expressions in foreign keys. Only columns. So a foreign key on upper(col) won't work:

create table t1 (
  x varchar2(10) not null primary key
);

create table t2 (
  y varchar2(10) not null
);

insert into t1 values ('THIS');
insert into t1 values ('THAT');

insert into t2 values ('THIS');
insert into t2 values ('that');

alter table t2 add constraint fk 
  foreign key (y) references t1 (x);

ORA-02298: cannot validate (CHRIS.FK) - parent keys not found
  
alter table t2 add constraint fk 
  foreign key (upper(y)) references t1 (x);

ORA-00904: : invalid identifier


But you can add constraints to virtual columns. So you could add a generated column to t2 on upper(col). Then place the FK on this:

alter table t2 add z as (upper(y));

alter table t2 add constraint fk 
  foreign key (z) references t1 (x);
  
insert into t2 (y) values ('this');

select * from t2;

Y      Z      
THIS   THIS   
that   THAT   
this   THIS

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great,

Mari�n `alkovsk�, January 12, 2018 - 2:39 pm UTC

thanks. Jus maybe last one question - when there is index on upper(opera_user_id), a virtual column up as upper(opera_user_id) and FK on up, does it use the index?
Chris Saxon
January 15, 2018 - 11:30 am UTC

Does what use the index? Could you clarify please?

Index using

Mari�n `alkovsk�, January 18, 2018 - 11:13 am UTC

I was meaning the foreign key on virtual column, containing the function. Does it use index or the foreign key on virtual column can't access the index, even it is equal to the content of the virtual column?
Chris Saxon
January 18, 2018 - 1:28 pm UTC

I'm still not sure exactly what you're getting at...

But you can create an index on the virtual column and the database can use it:

create index i on t2 (z);

set serveroutput off
select * from t2
where  z = 'THIS';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT                              
EXPLAINED SQL STATEMENT:                       
------------------------                       
select * from t2 where  z = 'THIS'             
                                               
Plan hash value: 3892044154                    
                                               
--------------------------------------------   
| Id  | Operation                   | Name |   
--------------------------------------------   
|   0 | SELECT STATEMENT            |      |   
|   1 |  TABLE ACCESS BY INDEX ROWID| T2   |   
|   2 |   INDEX RANGE SCAN          | I    |   
--------------------------------------------


In fact, if you use the same expression on the source column as in the virtual one, that can use the index too!

select * from t2
where  upper(y) = 'THIS';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT                              
EXPLAINED SQL STATEMENT:                       
------------------------                       
select * from t2 where  upper(y) = 'THIS'      
                                               
Plan hash value: 3892044154                    
                                               
--------------------------------------------   
| Id  | Operation                   | Name |   
--------------------------------------------   
|   0 | SELECT STATEMENT            |      |   
|   1 |  TABLE ACCESS BY INDEX ROWID| T2   |   
|   2 |   INDEX RANGE SCAN          | I    |   
--------------------------------------------

Index of Virtual column used by FK

Mari�n `alkovsk�, January 18, 2018 - 1:59 pm UTC

Thanks, the last sentence is what I needed :)

In fact, if you use the same expression on the source column as in the virtual one, that can use the index too!
Chris Saxon
January 18, 2018 - 5:58 pm UTC

Great, glad it helped.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.