You are watching: Why do relational databases use primary keys and foreign keys?
All the examples for this lesson are based on Microsoft SQL Server Management Studio and also the AdventureWorks database. Use my Guide Getting Started Using SQL Server to learn SQL Server today!
What is the difference in between a Key Key and also a Foregime Key?
Before we can dig into the difference, let’s first check out major and also foreign crucial qualities. Let’s start out by finding out about major secrets.
Table of contents
In order for a table to qualify as a relational table, it should have actually a main essential.
The main key is composed of one or more columns whose data consisted of within are supplied to uniquely determine each row in the table. You have the right to think of them as an attend to. If the rows in a table were mailboxes, then the primary key would be the listing of street addresses.
In order to be a primary key, several conditions should organize true:The columns specifying the primary vital are distinctive.Each column might contain duplicate values; yet, the combination of column worths is distinctive.When specifying a table you specify the main crucial. A table has simply one major crucial, and also its interpretation is mandatory.The index maintains the major key’s uniqueness need. It likewise provides it straightforward for foreign essential values to refer earlier to equivalent major crucial values, as we will certainly learn around in the following area.
Looking to learn even more around database design? One of the first hurdles you’ll want to conquer is Database Normalization. Get our overview to help you through Database Design and also Normalization. We take a super complicated topic and make it simple for you to understand!
A international key is a set of one or more columns in a table that refers to the major key in one more table. Tright here aren’t any kind of unique code, configurations, or table meanings you need to location to officially “designate” a foreign vital.
In the diagram below look at the SalesOrderHeader table. The column SalesOrderHeader.CurrencyRateID is a international vital because it is regarded the CurrencyRate.CurrencyRateID. This column CurrencyRate.CurrencyRateID is the major vital of the CurrencyRate table.
Look at the following diagram. Which column is the international key?
If you shelp it was PersonPhone.BusinessEntityID then you are correct. The factor it is a international key is that it is referring to a main vital, Human.BusinessEntityID, on the other table.
Coincidentally, PersonPhone.BusinessEntityID is not just a foreign essential but is also part of PersonPhone’s main vital. The PersonPhone table’s major vital is the combicountry of BusinessEntityID, PhoneNumber, and PhoneNumberTypeID.
Here are international vital ideregarding keep in mind:Unprefer major tricks, international secrets can contain duplicate values. Also, it is OK for them to contain NULL values.Though not immediately created for foreign secrets, it is a great idea to define them.You can define several international essential within a table.
Wondering around database indexes? You have the right to learn even more around how indexes job-related here!
In the PersonPhone table have the right to you uncover the other international vital (view the answer at end of the article)?
Use Object Explorer to Find Database Keys
When you usage SSMS you’ll discover all sorts of beneficial information in the object traveler. You don’t need to dig deep to discover the primary keys. When you present a table’s columns, however clicking on the Columns folder, the primary essential columns have actually gold keys next to them.
Also, if any kind of of the international tricks are characterized in international essential constraints, which we’ll learn in the complying with area, then those columns have FK after them. These are circled in green in the above diagram.
Foregime Key Constraints
Some database monitoring systems, such as SQL Server enable you to put up foreign key constraints. These aid to enforce referential integrity. In their easiest form, a foreign crucial constraint stops you from entering worths that aren’t discovered in the connected table’s major vital.
Using the initially diagram as our example, you can’t enter the SalesOrderHeader.CurrencyRateID if it doesn’t already exist in the CurrencyRate table.
These constraints come right into impact in numerous ways:They bar you from changing the foreign vital value to one which doesn’t exist as a worth in the associated table’s major key.They speak you from deleting a row from the primary essential table. This stops you from developing orphan documents. Orphan records are described as “boy documents with no parents.”They soptimal you from including a international vital value that doesn’t exist in the main essential.
In summary, the constraints enpressure the partnership in between the main and foreign vital tables.
Comparichild of Main Keys to Foreign Keys
To summarize below is a comparichild of Primary to Foregime Keys
Answer to Question: Earlier we asked for you to identify the various other foreign vital in the PersonPhone table. The correct answer is PhoneNumberTypeID.
See more: Success Flowchart: Do You Have What It Takes? ?" Have What It Takes
Looking to learn even more around database design? One of the initially hurdles you’ll want to get rid of is Database Normalization. Get our overview to help you through Database Deauthorize and also Normalization. We take a super facility topic and make it easy for you to understand!
A primary essential is compelled to specify a relational database table. A foreign vital, is one or more columns for one more table that recommendations a table’s major vital. In some DBMS’s define international vital constraints to safeguard this connection.