Model Update Interpretation of PK

Toad Data Modeler
1

Has there been a change in how PKs are interpreted between the last two TDM versions for Model Update (and probable RE)? I see the Not Null attribute of fields in PKs going from True to False. Does TDM Model Update now only look for Not Null constraints for this attribute?

Thanks,
Dave Reed

Message was edited by: reedda

2

Hi Dave,

can you be a bit more specific or provide a screenshot of the difference, please? The version you mean are 4.0.6 and 4.1.5?

Thank you,
Lukas

3

Lucas:

When I do a Model Update, I am getting differences between the current model and the Oracle source for the the Not Null attribute of a field. The attribute value in the source (Oracle) is False and and in the destination (Model) is True. It happens to only PK fields, but not all of them. A screen shot is attached. I have also attached the table definition and constraints from Toad for the RELALG.ALG_DRAWING_PAGECONN table.

Thanks,
Dave

model update.jpeg
model update.jpeg960×557 329 KB

4

Lucas:

When I do a Model Update, I am getting differences between the current model and the Oracle source for the the Not Null attribute of a field. The attribute value in the source (Oracle) is False and and in the destination (Model) is True. It happens to only PK fields, but not all of them. A screen shot is attached. I have also attached the table definition and constraints from Toad for the RELALG.ALG_DRAWING_PAGECONN table.

Thanks,
Dave

table_constraints.jpeg
table_constraints.jpeg1368×742 568 KB

5

Lucas:

When I do a Model Update, I am getting differences between the current model and the Oracle source for the the Not Null attribute of a field. The attribute value in the source (Oracle) is False and and in the destination (Model) is True. It happens to only PK fields, but not all of them. A screen shot is attached. I have also attached the table definition and constraints from Toad for the RELALG.ALG_DRAWING_PAGECONN table.

Thanks,
Dave
table_def.pdf (58.1 KB)

6

Hi Dave,

False value for the Not Null attribute is correct, because when you look into the constraints definition from Toad, you cannot see Not Null constraint there. Therefore False value.
What version of TDM do you use?

Regards,
Lukas

7

Lukas:

Sorry for not getting back to you sooner. Off on other work. I’m using TDM 4.1.5.8. I would have thought that the PK should be sufficient. Correct me if I am wrong, but a PK enforces NOT NULL without a NOT NULL constraint.

Thanks,
Dave

8

Although it's true that PK enforces NOT NULL, but if the keyword NOT NULL is not stated for the attribute, the NOT NULL constraint does not get created. Toad Data Model takes this into account.

Behavior of TDM concerning that NotNull is based on the fact that, in Oracle, there is a difference whether the tables is created by a statement with the attribute having NOT NULL constraint (In the database the NOT NULL constraint is created then.):

CREATE TABLE tabname (atr INTEGER NOT NULL PRIMARY KEY);

or the following way without the NOT NULL constraint stated (In the database the NOT NULL constraint is NOT created then.):

CREATE TABLE tabname (atr INTEGER PRIMARY KEY);

TDM considers the above difference during RE and in the first case the NOT NULL attribute is set, in the other it isn't. It means that when generating the reverse-engineered database, the table gets created the same way as it was created originally.

Regards,
Lukas

9

Lukas:

Thanks, I understand now. One last question: On the ERD, Does TDM show NN for a PK field whether or not a NOT NULL constraint exists?

Dave

10

Hi,

TDM shows NN mark for attributes that will be generated as not null items. One more information for you: Click Settings | Options and in section Model - Physical Model check the checkbox Allow Null Attributes in Keys. Then edit your entity and see that the checkbox for NN specification will be enabled. When you look at SQL preview, you will see the difference.

Regards,

Vaclav