Re: Foreign Key question

Hi Dave,

that makes sense. You should read the documentation about FK. They can
be 1:n, 1:1, n:1. Normally i would make a unique field in each table to
avoid complex PK/FK. Eg a serial column.

Dave Clarke schrieb:
> 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
>

--

Daniel Schuchardt
/Softwareentwicklung/

www.prodat-sql.de <http://www.prodat-sql.de>