If possible, create a foreign key whose column size does not match

Question

I ran into what I consider to be strange behavior in Oracle

You can create a foreign key where the column size does not match the size of the link column, this seems wrong. Of course, the database should ensure that the column sizes match, am I missing something here?

I'm sure MySQL does not 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, of course, it is preferable if the FK matches the type and size of the data, there may be times when this is not practical (or even a good idea). In your case, the above PK is larger and, therefore, it will not cause problems in entering data into the second table, since they will only enter those that are 50 characters or less (to do the opposite, where the child table field is larger, it will be pointless sa, you you can never enter data in excess of PK). Depending on what the data is in the first table, this may actually prevent you from entering values ​​from the original table, in which you never need FK. However, you will have a problem if you want to associate a PK record with a length of 118 characters.

Let us look at the case of a table that was improperly designed, and the field was too large, and historically some data received what we want to save, but we will not communicate in any new tables. Perhaps now we apply a smaller size through a trigger or restriction or through an application. In the new child table, we only want to join the records that correspond to the current needs, and tera will never need to join the older longer records. In this case, creating a second table to get a shorter length is a good idea.

There are also cases where an intial table can contain different types of data (say, a keystore), and a child table can refer to only one type, and this type has a shorter length than is allowed in the parent table to account for different types. Again, using a shorter length would be nice.

I would suggest that for some reason such scenarios are allowed. However, simply because something is allowed does not make it a best practice. If I created the table, and I had no specific reason why the field should be different, then I would create them the same way. It's like cursors, they exist for specific 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, rather than simply relying on something to be possible.

Answer 2

The primary key column size is higher, so theoretically it should not cause any problems.

But when the condition is a different way, since the size of the primary key is smaller, this will cause problems.

But the best way should be the same type and size of data.