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