One-to-One
How a one-to-one relationship uses the child table's primary key as its foreign key, and why you would move rarely used data into its own table.
Apprentice · SQL · Database · Updated
The One-to-One relationship is the least common type of relationship in a database. A One-to-One relationship is used when you want to ensure that the parent table can only refer to a single row in a child table.
This can be accomplished by using the Primary Key of the child table as both the Primary Key as well as the Foreign Key. This ensures that the Foreign Key can only appear once in the child table.
We may want to store data that only applies to some of our customers. If we added those columns directly to the customer table, there would be many rows with null values. By putting the rare data in its own table, we could minimize the wasted space.
For example, a customer may apply for a store credit card. The Customer table is the parent table and the customer_credit table is the child.

Do it: update the store cards, one row per customer
The store keeps its credit cards in their own table, CustomerCredit, because most customers don't have one. The credit department has sent two changes.
The two tables (the Tables tab shows the SQL that makes them):
Customers, the parent table:
| CustomerId | CompanyName |
|---|---|
| 1 | Alfreds Futterkiste |
| 2 | Around the Horn |
| 3 | Bon app' |
| 4 | Seven Seas Imports |
CustomerCredit, the child table. Its CustomerId is both its primary key and its foreign key, so each customer can have only one row here:
| CustomerId | CreditLimit |
|---|---|
| 1 | 1000 |
| 2 | 750 |
The two changes:
- Bon app' (
CustomerId3) was approved for a store card with a credit limit of 500. It has no row inCustomerCredityet. - Around the Horn (
CustomerId2) already has a card. Its credit limit goes up from 750 to 1500.
A second row for customer 2 would be refused: its CustomerId is already in the table, and a primary key can't appear twice. So a new card is a new row, and a new limit changes the row that's already there.
After your SQL runs, CustomerCredit should hold exactly these rows (shown by CustomerId):
| CustomerId | CreditLimit |
|---|---|
| 1 | 1000 |
| 2 | 1500 |
| 3 | 500 |
Your job: write two statements, one for each change.
- Add a row to
CustomerCreditfor Bon app':CustomerId3,CreditLimit500. - Change Around the Horn's row,
CustomerId2, so itsCreditLimitis 1500. - Leave the other rows as they are: Alfreds Futterkiste keeps 1000, and Seven Seas Imports still has no card.
Practiced live on the lesson page: write the SQL right there and check it.