Skip to Content
0

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

Dec 07, 2017 at 01:33 PM

63

avatar image
Former Member

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
George McGeachie Dec 07, 2017 at 02:56 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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?

0
George McGeachie Dec 12, 2017 at 09:53 PM
1

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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Excellent, thanks George.

I think my problem was that I generated the physical from a logical model, and the "Add" code above obviously doesn't get applied in that case. I guess I'll probably need to do something similar for the "Add" code for logical models, to change my 'Primary UID' stereotype to a 'Reference UID' stereotype on the new attribute when creating a relationship. The physical model can specify SERIAL and INT data types for these, respectively.

0

You shouldn't need to do anything in the LDM to fix this, it's specific to the DBMS - that's where the database definition is applied. Which DBMS are you using?

0