Foreign Key question

From: Dave Clarke <pigwin32(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Foreign Key question
Date: 2009-06-01 21:42:29
Message-ID: ea6dff71-f0d1-4f10-826a-8aeabd6a16ec@n8g2000vbb.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello

I have a table that I'm trying to refactor and I'm by no means a SQL
expert (apologies if I'm posting to the wrong group). The table in
question has a column that allows NULLs. I want to move that column
into a separate table and set up a FK reference back to the original
table. My question is whether this is the correct way to refactor this
table.

Original table (other columns elided)

PurchaseOrder
---------------------
POType
PONum
ServiceProviderNum
WorkOrderRef (NULLs allowed)

PK: POType + PONum
Candidate Key: PONum + ServiceProviderNum

Proposed structure

PurchaseOrder
---------------------
POType
PONum
ServiceProviderNum

PK: PONum + ServiceProviderNum

WorkOrder
---------------
PONum
ServiceProviderNum
WorkOrderRef (NULLs not allowed)

PK: PONum + ServiceProviderNum
FK: PurchaseOrder( PONum + ServiceProviderNum)

Does that make sense? My intention is to be able to join PurchaseOrder
and WorkOrder to get the set of PurchaseOrder's that have been
assigned WorkOrderRef's. As I understand it, FK's are generally used
for 1 to many relationships where as this is expressing a 1 to 1
relationship.

I would be very grateful for any assistance with this. Thanks, Dave

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Bailey 2009-06-01 22:05:02 Re: xml to table (as oppose to table to xml)
Previous Message j-lists 2009-06-01 21:04:03 Re: Foreign key verification trigger conditions