Adding foreign key constraint to 'partial' primary key in PostgreSQL

Question

I am trying to add a foreign key constraint to a table in my PostgreSQL 8.4 database, but it is failing as the target field, though part of a multi-column primary key, is not in itself unique.

The database has the following structure:

Table 1 (names of primary IDs):

PrimaryType, Name
[Primary key = "PrimaryType"]

Table 2 (names of child IDs for each type of primary ID):

PrimaryType, SubType, Name
[Primary key = "PrimaryType, SubType"]
[Foreign key = "Table2.PrimaryType = Table1.PrimaryType"]

Table 3 (logs which include a primary and child ID):

PrimaryType, SubType, DATA1, DATA2, ..., DATAN
[Foreign key = "Table3.PrimaryType = Table1.PrimaryType" AND "Table3.SubType = Table2.SubType"]

Obviously, the second part of the foreign key for table 3 is what is causing the problem. I just need to ensure that the primary and subtype ID pair in the log is a valid combination.

Thanks in advance.

Wayne Conrad · Accepted Answer · 2010-02-17 13:40:29Z

2

For table 3's foreign key, change:

foreign key PrimaryType references Table1(PrimaryType)
foreign key SubType references Table2(Subtype)

to

foreign key (PrimaryType, SubType) references Table2(PrimaryType, SubTYpe)

answered Feb 17, 2010 at 13:40

Wayne Conrad

109k26 gold badges158 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding foreign key constraint to 'partial' primary key in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related