Composite Primary Key Concept in Adventure Work Database
I am wondering why the EmailAddress work table in the Adventure database uses a composite primary key (BusinessEntityID, EmailAddressID (Identity))? if it has to do with setting the cluster index for both fields, I would appreciate it if I could explain how to physically store the composite primary key (in what order and how to insert the data)?
source to share
It is precisely because the same email address can be used by several persons, and also the same person can use multiple email addresses, i.e. makes the relationship between a person and an email address many, to many.
If the need was to ensure that the email was owned by a person, that would be enough to make it a foreign key and the column BusinessEntityID
doesn't matter.
Update:
There are 2 tables involved, Person
and EmailAddress
.
Each entry is Person
indicated by a symbol BusinessEntityID
. To associate a record from Person
with a record in another table T
, you just need to include the column in that table T
that belongs to BusinessEntityID
. Now, if we need to make sure that all records in T
must be associated with any record in Person
, then we put the foreign key constraint on T.BusinessEntityID
and invalidate it. If, furthermore, we wanted to make sure that each record in T
must be associated with one and only one record in Person
, then we could set a unique constraint on the column T.BusinessEntityID
.
When we make 2 columns A
and B
part of the primary key of a table, we are telling the database that the values of these two columns together must be unique for all records in that table. It has nothing to do with the values in each of these columns or foreign key relationships.
To illustrate:
Person (BusinessEntityID, Name) and PK is BusinessEntityID
---------------
1 | John
---------------
2 | Jane
---------------
3 | Sales Team
EmailAddress (BusinessEntityID, EmailAddressID, EmailAddress) and PK is [Business EntityID, EmailAddressID] where EmailAddress is auto-incremented
--------------
1 | 1 | john@example.com
------------------------
1 | 2 | john@contoso.com
------------------------
2 | 3 | jane@example.com
------------------------
2 | 4 | jane@contoso.com
------------------------
1 | 5 | sales@example.com
------------------------
2 | 6 | sales@example.com
------------------------
3 | 7 | sales@example.com
Data similar to the above can be put into tables in your example. Now what's going on here?
There are 3 organizations, John, Jane and the sales team.
John has 2 personal email addresses. Jane also has 2 personal email addresses. In addition, the email address sales@example.com
belongs to the sales team, but also to John and Jane.
This is a many-to-many relationship.
In addition, if the composite key in EmailAddress is clustered, the keys are stored in the order in which they appear. Read more.
source to share