September 8, 2018 at 5:16 pm
Hi Friends,
I am a Newbie to T-SQL Database Development. My question is I notice that in Adventureworks2012 Database there is a primary key column named BusinessEnitityID in a table named HumanResources.Employee that also has a foreign Key constraint on the same column, and I was just wondering why is this? Also I notice that in Adventureworks2012 there are tables that have multiple columns with foreign key constraints, and I was wondering what is the purpose for having multiple foreign key constrains on a table? Thanks !
September 10, 2018 at 7:53 am
A few things. First, Adventureworks is built as a sample to show off features for Microsoft. It's not necessarily a great database design.
A PK is for uniqueness in a table.
A FK is to ensure parent/child records exist without orphans.
Both can be important in situations. There are times that I have a FK to another table that is part of a PK on this table.
When you say multiple columns, you're not being clear. To you mean multiple columns make up a FK or that multiple columns have separate FKs to different other tables? Either are fine.
September 10, 2018 at 11:31 am
Thank You for your help. I understand why a table can have multiple foreign keys. But you commented about sometimes you will have both a foreign key and primary key constraint one a single column. If you don't mind could you please share with me particular instances in your design why this practice would be necessary. Just using my own intuition, is this to establish the uniqueness of a foreign key constraint in a many to many relationship? And is this practice also used to avoid having to use a junction table? Again, Thanks for your help.
September 10, 2018 at 1:32 pm
Well the reason you might have FK columns as part of the primary key is if they are part of what makes the record unique, it also enforces not null etc... A common case might be something like an order item table, with the primary key of a line item id and a FK to the order table. And no this is not a method to avoid having to use a junction(or intersection) table, those are used to support a different type of relationship, something you might see in that case would be a primary key on the junction table made up of two FK's.
September 10, 2018 at 1:47 pm
In the example scenario that you used would you combine the primary key and the foreign key so that your orders would be unique? Because I thought that would be the purpose of having a primary key on the orders table?
September 11, 2018 at 12:14 pm
The only place I can think of off the top of my head for the PK and FK to be the same column (or columns) is a vertical partition.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply