I was talking to Mathias Magnusson about this. Which inspired me to write this post.
Do we need to specify the data type when we are creating a foreign key column? No, you can easily create a foreign key column without specifying the actual data type.
create table parent_tbl ( parent_pk number(10) primary key ); Table PARENT_TBL created. create table child_tbl ( child_pk number(10) primary key, child_fk references parent_tbl ( parent_pk ) <-- no datatype ); Table CHILD_TBL created.
The data type of the child_fk column is not needed, because Oracle automatically sees to that it inherits the data type of the referenced key column.
I like this since this ensures consistency and it gives the optimizer what needs for doing all those joins in a proper way.
Another interesting aspect of this is when you add a foreign column without specifying the data type to an existing table which normally might be a very expensive operation. The performance benefit might be huge due to less logical reads. I recommend reading Oren Nakdimon blog post on that topic.
Over & Out