I think you may be running into the now fixed (for 7.1) bug where
ADD CONSTRAINT ... FOREIGN KEY got the column ordering wrong when checking
existing data. I may be able to build a patch against 7.0.x since the
fix is relatively minor if you don't plan to upgrade when 7.1 comes out.
On Mon, 5 Feb 2001, Panon, Paul-Andre wrote:
>
> For a project we are working on, I have created a custom postgresql data
> type which is similar to MS SQL Server's uniqueidentifier data type. It uses
> dynamic link library extension that calls the FreeDCE library to generate
> GUIDs. Support for the data type and support functions is added to a
> PostgreSQL database using the attached SQL script. The functions all seems
> to work fine, including use of merge sorts and hash joins during SQL JOIN
> statements when using the data type as part of a primary key. However
> adding foreign key constraints sometimes causes a problem.
>
> I never have a problem adding a foreign key to a parent table with a
> multi-part key as long as the child table is empty. Adding data to the child
> entity afterwards seems to properly enforce RI. However, if data exists in
> the child entity, an RI check is performed on the existing data and this
> check sometimes seems to break. As far as I can tell, the RI check in the
> latter case seems to confuse the order the Key parts in either the Primary
> Key or the Foreign Key. In the case of a multi-part key RI, it was
> complaining that it couldn't perform a type conversion between the type of
> two different key parts of the primary key.
>
> So in a database with the following table definitions (OK I know it isn't
> exactly great DB design to have 4 uniqueidentifiers in a PK, but please bear
> with me) :
