Update Model from Database: NOT NULL Constraint of primary key is missing

[dirk.mika1]

Hello, everybody,
i am trying to update an older existing model with "Update Model from Database".
But the NOT NULL constraint of the primary key columns is always lost.
I have already tried all possible options that could influence this, without success.
Let me illustrate this with an example:

This is how the ACCESS_OBJECTS table is defined:

This is what the Model Update Wizard shows for this table:

Is there any option or setting, which may cause this?

BR
Dirk

[Michal.Vasut]

Hello Dirk, can you provide more details about version of Toad Data Modeler and database (name & version)? We need such details to simulate the problem.

Thanks,
Michal

[dirk.mika1]

Hello,

sure. It's Toad Data Modeler 7.1 but I've checked the latest beta as well.
The Database server is Oracle 10.2.0.5.

BR
Dirk

[Mario.Tkacik]

Hi Dirk,

False value you see in Model Update Wizard 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.

PK enforces behavior of attribute as not Nullable, 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.

If you want a chance to not set NOT NULL constraint on attribute then you go to Options (menu Settings > Options) and in tree "Model > Physical Model" check checkbox "Allow Null Attributes in Keys".

Regards,

Mario

[Michal.Vasut]

And if you want to preserve values from your model do following in Model Update Wizard:

• go to step Select Object Types and switch to Detailed Settings
• there expand Entities > Attributes and you will see Not Null option, uncheck it

Michal

[dirk.mika1]

Thank you very much for the detailed explanation. I do, however, have one further comment.
I have created table AAA for a test:

CREATE TABLE aaa
(
   id      NUMBER PRIMARY KEY,
   col1    VARCHAR2(100 CHAR) NOT NULL,
   col2    VARCHAR2(100 CHAR)
);

After an "Update Model from Database" this table looks like this in TDM:

The NOT NULL is checked for the ID column although not explicitly specified as constraint on table creation.

If I do another "Update Model from Database" I again get the effect as described above:

I don't want to uncheck the Not Null option in the Update Wizard, because there may be changes in non PK columns I like to merge.

BR Dirk

[Mario.Tkacik]

Please try uncheck "Allow Null Attributes in Keys" on Options before run "Update Model from Database".

Mario

[dirk.mika1]

If you mean the option under "Model / Physical Model", it was already unchecked.

BR
Dirk

[Mario.Tkacik]

I am sorry, I mean "check" this setting "Allow Null Attributes in Keys".

Mario

[dirk.mika1]

Now table AAA is no longer displayed as different.
However, most of the other tables are still displayed as different.

But, after checking this option, the AAA table now looks like this in TDM:

But I think I can live with that. I will take over the Not Null attribute and that should solve the problem.