Celestial Bodies Database - Build a Celestial Bodies Database

Backend Development
1

Tell us what’s happening:

I’m struggling to structure my actual database. I can’t understand how primary keys and foreign keys relate to each other.

From what I understand, the values of the primary key and the foreign key should be the same, and I can’t work out how to do this. I’ve seen other questions, and have used moon_id etc as keys which makes it even more confusing as they wouldn’t be the same value. Can someone explain if I’m understanding this right and how to I could start to get the ball rolling?

Your code so far

+--------+----------------------+----------+--------------+
| Schema |         Name         |   Type   |    Owner     |
+--------+----------------------+----------+--------------+
| public | galaxy               | table    | freecodecamp |
| public | galaxy_galaxy_id_seq | sequence | freecodecamp |
| public | moon                 | table    | freecodecamp |
| public | moon_moon_id_seq     | sequence | freecodecamp |
| public | planet               | table    | freecodecamp |
| public | planet_id_seq        | sequence | freecodecamp |
| public | star                 | table    | freecodecamp |
+--------+----------------------+----------+--------------+
(7 rows)

+---------+------+------+-----------------+
| moon_id | name | size | orbiting_planet |
+---------+------+------+-----------------+
|       1 | Moon | 2159 | Earth           |
+---------+------+------+-----------------+

+-----------+---------+-------------------------+----------+---------------------------+
| planet_id |  name   | times_bigger_than_earth | has_life | distance_from_earth_in_au |
+-----------+---------+-------------------------+----------+---------------------------+
|         1 | Mercury |                    0.38 | f        |                      1.45 |
|         2 | Venus   |                    0.95 | f        |                      1.74 |
|         3 | Earth   |                    1.00 | t        |                      0.00 |
|         4 | Mars    |                    0.53 | f        |                      2.67 |
|         5 | Jupiter |                   11.24 | f        |                      6.45 |
|         6 | Saturn  |                    9.45 | f        |                     11.05 |
|         7 | Uranus  |                    4.00 | f        |                     21.09 |
|         8 | Neptune |                    3.88 | f        |                     31.32 |
+-----------+---------+-------------------------+----------+---------------------------+

+-----------+-----------+------------+
| galaxy_id |   name    |    size    |
+-----------+-----------+------------+
|         1 | Milky Way | 100000.000 |
+-----------+-----------+------------+

Your browser information:

User Agent is: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Safari/537.36

Challenge Information:

Celestial Bodies Database - Build a Celestial Bodies Database

2

you need a new column in the tables you want to link to an other table

for example if you want to add to which planet the moon belongs to connecting to the planet table, you need a planet_id column, and then add the foreign key relationship

3

Oh right. Thank you. I think I get it now. I thought I had to link them through the same star_id, planet_id we use to reference each item and couldn’t work out how it would work, but what I’m getting from your answer is we need to add a column (foreign key ) specifically to link to other databases?

4

yes, you need a column to work as foreign_key

5

I think I’ve got it working now. Thanks :slight_smile: