On 28 Aug 2003, Gregory Stark wrote:
> It seems when you create a new table with the "references" syntax the
> constraint is created with a dependency specifically on a "primary key"
> constraint on the target table.
>
> However when you alter a table to add a foreign key constraint the constraint
> is added with a dependency on any unique index on the column -- not
> necessarily a primary key constraint.
Well, it shouldn't be limited to a primary key constraint (you must be
able to reference a non-primary key unique constraint). If we didn't
need to worry about backward compatiblity, we could make it dependant on
the unique/primary key constraint, not the underlying index (because
upgraded old systems might only generate a unique index). However, that
would only help if the unique constraint were created before the
references constraint.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
