Skip to Content
0

Setting Constraint Name

Aug 28, 2017 at 02:26 PM

37

avatar image
Former Member

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Phillip Lam
Aug 29, 2017 at 09:01 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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

0