Skip to Content
avatar image
Former Member

VALIDATE/NOVALIDATE DISABLE/ENABLE constraint options in generated script

Oct 23, 2017 at 08:31 AM | 439 Views

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).

SAP PowerDesigner
constraint | notnull | novalidate
Add comment
10|10000 characters needed characters exceeded

  • Former Member
    Oct 23, 2017 at 11:35 AM

    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?

  • Get RSS Feed

3 Answers

  • avatar image
    George McGeachie
    Oct 31, 2017 at 10:39 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    George McGeachie
    Oct 26, 2017 at 02:25 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Oct 30, 2017 at 11:11 AM

      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.

  • avatar image
    George McGeachie
    Oct 30, 2017 at 11:31 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded