Toad World® Forums

Foreign Key Modeling - Cannot alter child key data type


#1

Hi All,

I have come accross the following problem while reverse engineering our company’s physical data model. We are using Oracle 10g R2 and our TDM version is 3.5.10.4

In example, I have the following case in DB:

create table t1 (a number(3));
create table t2 (a number(5), b number(2));
alter table t1 add constraint pk_t1 primary key (t1);

I have reverse engineered the tables and the primary key into TDM and I would like to create a foreign key constraint from t2 to t1. When I add the relationship on TDM’s physical model, the data type of the referencing column in the child table (t2.a) automatically becomes the same as the referenced column in the parent table’s (t1.a). By the way, I have already tried changing the foreign key column of the child table with F2 key… I believe this case is not related with the F2 thingy.

It is possible to create a foreign key constraint on Oracle without changing the data types: alter table t2 add constraint fk_t2 foreign key (a) references t1(a); is it not possible to create such a relationship on TDM 3.5. while keeping the original data types?

I was expecting TDM to let me create the relationship but display a warning during model verification.

I hope I could explain the case, thanks,

Burcu

Message was edited by: burcu.orbay_1238172634468


#2

Hi,

I don’t see any benefit of having different data type lengths in PK/FK columns in parent and child tables.

How can you store Number(5) value into a column that has reference to Number(3) column?

To my answer: TDM doesn’t allow you to model such scenario visually. If you want, you can add your Alter Table statement to tab After Script, but you have to write it there manually.

Regards,

Vaclav


#3

Hi Vaclav,

You are absolutely right, but this feature would help the data architects to detect modeling errors directly from TDM. However as long as TDM automatically converts data types, this will not be possible.

By the way, a FK where a Number(5) value column that has reference to Number(3) value column can be stored (the database allows it). The DB will allow insertion of data as long as the referenced key exists in the parent table. I know that it is not a correct modeling practice, but corporate databases which have hundreds of tables and which have been evolving for years do have this sort of unnoticed and unharmful faults.

Besides, there can be cases where FKs are not in use (disabled), but TDM still converts the data types during reverse engineering. I believe the “reverse engineering” feature should represent the DB objects as they really are. Otherwise it is not reverse engineering.

I have been evaluating other modeling for some time and some do not change data types during reverse engineering and they also allow the explained case (referencing from number(3) to number(5) ) because the DB also allows it. They report the data type mismatch with a warning during model verification.

I hope this case will be included into a new TDM release in the near future.

Best Regards,

Burcu


#4

Hi Burcu,

thank you for sending me detailed explanation. In fact, if TDM allowed users to model something what I feel is incorrect, then it would bring more problems than advantages (think about comparison/synchronization features etc., what should happen when you change Number3 to Number4 in parent, should the Number5 data type be converted in child too, or not? etc. etc.)

I understand there can be cases when someone changed data type in parent from Number5 to Number3, but if the column in child table was not modifited to Number3 too, then I believe this was a partial solution. I belive the key question is not if it should be possible to model such situation in modeling tools, but if the database should allow it.

If you want to see errors in DB, please do Reverse Engineering and look at the Message Explorer. You will see warnings.

Regards,

Vaclav