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)