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