Skip to Content
avatar image
Former Member

Setting Constraint Name

Hi all,

just wondering, is it possible to configure the DBMS Extension (in our case Oracle11g) to somehow allow PD to number constraints automatically?

The current setting are as follows:

FK_%3.12UM:CHILD%_%3.12UM:PARENT%

The Problem are the FK Constraints between two Tables, f.e.

Reference 1

Tabel 1: D_STA_UMS_GP_KONTRAH

Tabel 2: D_NTF_UMS_GP_KONTRAH

Reference Name: FK_STA_UMS_GP_KO_NTF_UMS_GP_KO

Reference 2

Table 1: D_STA_UMS_GP_KONGEN

Table 2: D_NTF_UMS_GP_KONGEN

Reference Name: FK_STA_UMS_GP_KO_NTF_UMS_GP_KO

So at the end, we have two references, with the same Constraint Name, what obviously leads to an error during Model Check.

We would like to have something like this:

Reference 1

Tabel 1: D_STA_UMS_GP_KONTRAH

Tabel 2: D_NTF_UMS_GP_KONTRAH

Reference Name: FK_STA_UMS_GP_K_NTF_UMS_GP_K01

Reference 2

Table 1: D_STA_UMS_GP_KONGEN

Table 2: D_NTF_UMS_GP_KONGEN

Reference Name: FK_STA_UMS_GP_K_NTF_UMS_GP_K02

How to achieve that?

Thanks,

Rafal

fk-naming.jpg (72.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 29, 2017 at 09:01 PM

    Changing DBMS is not a good way in this case. Because you need find place to store all references.

    Instead, you can run script or use script in transformation to achieve the goal.

    The following is example: ( Open your model and run the code)

    Set dict = CreateObject("Scripting.Dictionary")


    set model=activemodel
    for each ref in model.references
    FKeyname=ref.GetAttributeText("ForeignKeyConstraintName")

    if dict.exists(FKeyname) then
    number= dict.item(FKeyname)
    number=number + 1
    ref.ForeignKeyConstraintName= FKeyname & number
    dict.remove FKeyname
    dict.add FKeyname, number
    else
    dict.add FKeyname, 1
    end if
    next

    for each ref in model.references
    FKeyname=ref.GetAttributeText("ForeignKeyConstraintName")
    if dict.exists(FKeyname) and dict.item(FKeyname)>1 then
    ref.ForeignKeyConstraintName= FKeyname & 1
    end if
    next

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Phillip,

      thanks for you answer. I've done it already yesterday, also in a Transformation on Model level, but with a bit less complicated script... I believe. Seems to work as well....

      For Each pkg in obj.Packages
      For Each ref in pkg.References
      'GTL -> FK_%3.12UM:CHILD%_%3.12UM:PARENT%
      ref_name = ref.GetAttributeText("ForeignKeyConstraintName")
      cnt = 0
      For Each ref_ex in pkg.References
      If ref_ex.GetAttributeText("ForeignKeyConstraintName") = ref_name and ref_ex <> ref Then
      cnt = cnt + 1
      End If
      Next
      If cnt > 0 Then
      ref.SetAttributeText "ForeignKeyConstraintName", ref.GetAttributeText("ForeignKeyConstraintName") + "_" + Cstr(cnt)
      End If
      Next
      Next

      BR,

      Rafal