VALIDATE/NOVALIDATE DISABLE/ENABLE constraint options in generated script

[Former Member]

Is it possible in the logical or physical model to modify the column NOT NULL constraint to have DISABLE and/or NOVALIDATE options? I'm using PowerDesigner 16.6.2.0 (5220).

[Former Member]

In the opposite direction when doing the database reverse engineering there seems to be connected problem. Having the table created with:

CREATE TABLE BASE.TABLE_TEST2 (

DBID INTEGER,

NOTNULLDISABLE VARCHAR2(10 BYTE) DEFAULT ' ',

NOTNULLNOVALIDATE VARCHAR2(4 BYTE) DEFAULT '0',

NOTNULLDISABLENOVALIDATE VARCHAR2(1 BYTE) )

...

ALTER TABLE BASE.TABLE_TEST2 ADD (

CHECK ("NOTNULLDISABLENOVALIDATE" IS NOT NULL) DISABLE NOVALIDATE,

CHECK ("NOTNULLNOVALIDATE" IS NOT NULL) ENABLE NOVALIDATE,

CHECK ("NOTNULLDISABLE" IS NOT NULL) DISABLE VALIDATE...);

When reverse engineering this table into the model the NOT NULL checks are ignored and not created in the model.

Is it expected behavior of the PowerDesigner?

[GeorgeMcGeachie]

The GTL code for defining a column check constraint is

alter table [%QUALIFIER%]%TABLE%
add [%R%?(][constraint %CONSTNAME% ]check (%.A:CONSTRAINT%)[%CheckDeferrable%? [.Z:[deferrable][%CheckInitiallyDeferred%? initially deferred: [%R%?initially immediate]]]:[%R%? not deferrable]][%R%?)]

Here's the location in the database definition:

ORA12C::Script\Objects\Column\AddColnChck

It doesn't mention Enable or Validate - these aren't properties of the Column. Youd could add them yourself, and amend the GTL code, or you could raise a case with SAP, as a feature of Oracle (if that is the DBMS) is not supported. They could sort it pretty fast, and issue a new database definition file.

[Former Member]

The issue was registred as KBA2561756

[GeorgeMcGeachie]

Hmm, it does change when I make changes in my sample model. Using the Oracle 12c DBMS, I set the following on a Reference:

and here's the SQL preview:

Now if I change the options and uncheck Disable and Validate, the SQL changes:

With some experimentation I can see that NOVALIDATE doesn't appear if I check Disable and uncheck Validate.

I'm not an expert on Oracle, so I can only assume that this is valid :). You can see the code that creates this part of the SQL in the DBMS definition file - "Profile\Reference\Extended Attributes\refrOptions"

Sorry I can't help you any more, as I don't know what you mean by "not null check level" in this context.

[Former Member]

George, I appreciate your help :-). By "not null check" level i mean the statement that i included in the comment above and here:

enable-novalidate.jpg

Probably my description was not clear. It was never about the FK CONSTRAINT, but about the NOT NULL CHECK :-).

[GeorgeMcGeachie]

I think what you're looking for is on the DBMS-specific tab in the properties of the Reference - e.g. the tab may be called Oracle

[Former Member]

True that on the FK constraint there is a "Disable" constraint option, though "Validate" seems not to work - no effect on the SQL script seen on Preview tab either it is on or off.

Nevertheless it does not help me at all, since i would like to set DISABLE and/or NOVALIDATE on the not null check level. If I for example i wanted to do this on the DB level:

ALTER TABLE SCHEMA.TABLE ADD ( CHECK ("COLUMN2" IS NOT NULL) ENABLE NOVALIDATE);

I have a hard time trying to model this change on the physical model level in the PowerDesigner.