Toad World® Forums

BUG with relationships and serial datatypes on postgres


#1

POSTGRES 8.2

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’.

Regards,

BillR
toad.log (31.5 KB)


#2

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!

Regards,

Vladka


#3

Good news!

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

Thanks again!

Vladka