Forum Stats

  • 3,768,537 Users
  • 2,252,807 Discussions
  • 7,874,614 Comments

Discussions

Synchronize table level check constraint

mmagan-Oracle
mmagan-Oracle Member Posts: 30 Employee

I have defined a table level constraint:

date_id = trunc(date_id)


When I run "Synchronize Data Dictionary with Model", the following DDL is consistently generated:


ALTER TABLE pm_data_mart.dmt_date_dimension DROP CONSTRAINT dmt_date_dimension_ck_1;

ALTER TABLE pm_data_mart.dmt_date_dimension

   ADD CONSTRAINT dmt_date_dimension_ck_1 CHECK ( date_id = trunc(date_id) );


If I run the two statements and then repeat "Synchronize Data Dictionary with Model", I would expect no DDL but the same two statements are generated again.

Why is this happening and how can it be prevented?

PS: Version 21.1.0.092 Running against Oracle 19c database.

Answers

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,352 Employee

    Hi,

    Why is this happening and how can it be prevented?


    This can be prevented if you move to newer version of DM. I tested with 20.1 and it works properly. And now 21.2 is the actual version.

    There was a problem with table level check constraint defined over single column - during import they are treated as column level check constraint and that brings the problem with recreated table level check constraint. It's fixed, I don't recall in which version but it works at least in 20.1


    Philip

  • mmagan-Oracle
    mmagan-Oracle Member Posts: 30 Employee

    I am actually on Version 21.1.0.092 . If I run “Check for Updates”, then no updates are displayed (i.e. I do not see 21.2).