Thread: Foreign Key question
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
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:
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
In response to Dave Clarke <pigwin32@gmail.com>: > > 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 You can certainly do what you're describing and it will work well. I am curious as to why you'd want to, though. What problem are you trying to solve by doing this? I don't see it being worth the extra complexity and size you've added to the schema. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
On Jun 3, 1:04 am, wmo...@potentialtech.com (Bill Moran) wrote: > In response to Dave Clarke <pigwi...@gmail.com>: > > > > > > > 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 > > You can certainly do what you're describing and it will work well. I > am curious as to why you'd want to, though. What problem are you trying > to solve by doing this? I don't see it being worth the extra complexity > and size you've added to the schema. > > -- > Bill Moranhttp://www.potentialtech.comhttp://people.collaborativefusion.com/~wmoran/ > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Thanks for the responses. I suspect I'm trying too hard, e.g. Bill's comment re extra complexity. I'll have a ponder over the next couple of days. I'm more of an OO guy and I'm working in an environment with an existing ORM product. I want to feel confident I've got the relational structures correct.