Should it be possible to create a Foreign Key where column size doesn't match

Question

I came across what I think is weird behavior in Oracle

It is possible to create a foreign key where column size doesn't match reference column size, this seems incorrect. Surely a database should enforce matching column sizes, am I missing something here?

I'm fairly sure MySQL doesn't allow this

SQL> create table parent(col1 varchar2(255) primary key);
Table created.
SQL> create table child(col1 varchar2(20) primary key, constraint col1_fk foreign key (col1)
references parent(col1));
Table created.

Answer 1

While it certainly is preferred if the FK matches in both data type and size, there may be times when it is not practical (or even a good idea). In your case above, the PK is larger and thus this will not cause a problem in data entry to the second table as they will only enter those that are 50 characters or less (doing the reverse where the child table field is larger would be pointless sa you could never enter data larger than the PK has). Depending on what the data is in the first table, that could actually prevent you from entering values from the orginal table that you do not ever want an FK to. However, you would have a problem if you wanted to link to a PK record that was 118 characters long.

Let's look at the case of a table that was orginally badly designed and the field was too large and historically some data got in that we want to keep, but will not be linking to in any new tables. Perhaps we now enforce the smaller size through a trigger or a constraint or through the application. In a new child table, we only want to be able to join to the records that match the current need and tehre will never be a need to join to the older longer records. In this case designing the second table to only take the smaller length is a good idea.

There are also cases where the intial table may contain different types of data (Say a key value store) and the child table may want to link to only one type and that type has a smaller length than is allowed in the parent table to account for the different types. Again using the smaller length would be a good thing.

I would imagine that scenarios like this are why it is allowed. However, just because something is allowed doesn't make it a best practice. If I was creating a table and I had no specific reason why the field should be different, then I would create them the same. It's like cursors, they exist for particular use cases, but they can be used for others where they are not the best choice. Database design involves knowing how to determine the best method, not just relying on the fact that something is possible.