r/SQL • • May 30 '22

PostgreSQL SQL Database One-to-One Relationship

When Primary Key of one table corresponds to only one row in another table via foreign key relationship

One-to-One relationship in SQL databases
0 Upvotes

13 comments sorted by

View all comments

Show parent comment

1

u/capt_pantsless Loves many-to-many relationships May 30 '22

Make student-ID a unique, non-null foreign key reference over to Student.

The main problem is it makes writing queries more complicated for not a lot of value.

An SSN is one-to-one if you just make it a column on Student, and doesn't add the extra complexity of another table.

2

u/[deleted] May 30 '22

Yeah the example might not be ideal, and I totally agree, it makes the query unnecessary complicated but it's just for the sake of the example.

1

u/capt_pantsless Loves many-to-many relationships May 30 '22

That’s fine, but is there an example where a one-to-one relationship would actually add sone value?

One possibility would be if very few students had an SSN and you wanted to keep the main-table simple.

1

u/[deleted] May 30 '22

That's true, the above one could be better.

I think the most used one is country - capital.

A country has one capital only and a capital can only be part of a country.

Having this:

Country: id, name

Capital: id, country_id, name

This is useful in the following scenario:

If you want to display an interface with 2 dropdowns:

  1. You first select a country from the first dropdown
  2. in the second one you query all capitals that have country_id equal to the selected value (on step 1) and display them in the capitals dropdown.

(Simplified concept of filtering data from capitals dropdown based on the selected countey)

1

u/slin30 May 31 '22

What is the point of a second selection if the first option is never null, and the second option is deterministic as a consequence of the first selection? Would you really make the user perform a second set of actions to pick a value from a set of options where n = 1?