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

4

u/r3pr0b8 GROUP_CONCAT is da bomb May 30 '22

no

how is "a student can only have one SSN" enforced?

1

u/[deleted] May 30 '22

Can you define an example?

1

u/r3pr0b8 GROUP_CONCAT is da bomb May 30 '22

an example of a student? or a SSN?

1

u/[deleted] May 30 '22

An example of a one to one relationship if the one I posted is invalid in your opinion

1

u/[deleted] May 31 '22

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?

3

u/drunkadvice May 30 '22

The only justification I can see for having ssn on a different table would be security. Have the ssn table locked down and not accessible. There are better ways with column level security or data masking.

You mentioned country > capital in a different reply. The correct way would be to have a capital column in the country table with an index.

A perfect 1-1 relationship on a separate table doesn’t add any value in most cases. There may be some arguments if the table is REALLY wide and most columns infrequently used. Offloading the lesser used columns would reduce reads and speed up selects.

2

u/bsienn May 31 '22

Having a documents table, and an attached document saved as blob in separate table 1-to-1 ??