Skip to Content

PowerDesigner Domain datatype as Serial, but want Foreign Key references to just use Number

I had a domain called 'UID' with datatype 'number' which I used for all my primary key attributes. Foreign key references would automatically get this type too, when adding relationships, which was all fine. I generate a physical model from this, and all works fine there too.

But I now want to change the UID domain to be of type 'Serial' in the physical model.

But this will be no good for the foreign key references, as they need to be just 'number'. It looks like I'll need to create two domains, one for primary keys (eg. 'UID-Primary'), and one for foreign key attributes (eg. 'UID-Reference'), which is annoying because every time I add a relationship I'd need to change the domain on the attribute.


Is there a much better and cleaner way to do this?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 07, 2017 at 02:56 PM

    I've never heard of anybody wanting to do this (though I don't get involved much with databases). If you change the Domain / Column rules in General Options to allow columns to diverge from the domain, that might work. The PK columns would inherit the data type from the domain, but the domain linkage is not passed to the FK columns. The FK columns would have data type = <undefined> - you could create a custom check to link these to a different domain, or just change the data type. The script would be quite simple.

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks George, I will experiment with the above.

      I would've expected this to be a common scenario. Whenever the 'Serial' type is used for a primary-key attribute, and a relationship created to that entity, the datatype of the referencing attribute would need to be changed, whether manually or by script. I wonder what the 'standard' approach is?

  • Dec 12, 2017 at 09:53 PM

    Via the internet, I can see that the Serial data type is supported by PostgreSQL and Informix (and probably others), so I experimented with creating a PK column with the Serial data type. In both cases, the FK column had a different data type.

    This appears to be handled in the database definition file, by code in the "Add" section for columns. For example, for Informix, the code (this is Generation template Language - GTL) reads:

    [%CLASS% ?
    [%NOTNULL% ?
    not null [%R%?constraint %CONSTNAME%] %20:COLUMN%]
    : %20:COLUMN% %30:DATATYPE%[%.6:DATATYPE%=SERIAL?[%ExtSerialStart%=0?:[(%ExtSerialStart%)]]] [default %DEFAULT%] [%NOTNULL%?not null [%R%?constraint %CONSTNAME%]]
    [%CONSTDEFN%]
    [%R%?[%ISPKEY%?primary key [constraint [%R%?[%QUALIFIER%]]%PKNAME%]]]
    ]

    I'm not familiar with all the variables in use here, but I can see a condition ([%.6:DATATYPE%=SERIAL? ), which tests if the first six characters of the data type are "serial". If you unscramble the nested square brackets, you ought to be able to to see what happens. The variables (within "% %") are defined in the PowerDesigner help file.

    Add comment
    10|10000 characters needed characters exceeded