Toad World® Forums

BUG with relationships and serial datatypes on postgres



There is a bug when a primary key is defined for a parent table as a serial data type and it is ‘exported’ to another table when an identifying or non-identifying relationship is created to another (child) table. This happens whether you define the primary key of the parent table with a ‘raw’ serial data type or create a dictionary type using a serial type.

For most if not all other data types this is generally acceptable behaviour. However for a serial data type this is NOT acceptable behaviour. The child table should take its foreign/primary key value from the parent. Therefore the correct data type in the child table for said column MUST BE an INTEGER, NOT a SERIAL. After all a serial type is really an integer that the database implicitly sets up a sequence for.

There is no way I have found to create a relation/referential integrity in TDM3 with a serial data type in the parent and the (correct) integer type in the child table. At least not on a Postgres 8.2 physical model.

Please take a look at the attached model. There are three tables: a ‘person’ table, an ‘address’ table, and a lookup table ‘person_address_relation’.


toad.log (31.5 KB)


Hello BillR,

Thanks very much for the bug notification. It is correct in PG 8.1, but in PG 8.2 there is this problem.
We will fix it. CR # 43 192.

Thanks for your great co-operation!




Good news!

This bug has been fixed. It will work properly in next Beta.

Thanks again!