Celestial Bodies Database - Foreign key

Dear friends

I can not make the foreign key exercise, I have fallow the rules without goed result, can somebody help me

thanks

Your browser information:

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

Challenge: Celestial Bodies Database - Build a Celestial Bodies Database

Link to the challenge:

hi there, to help you we will need some more information.

1- what error are you trying to resolve? (please paste the error message if any)
2- what is your database schema (use the \l and \d commands to get the relevant database and table schemas to show the setup of each table)

Im trying to do dit: Each “star” should have a foreign key that references one of the rows in galaxy I use dit comman ALTER TABLE star ADD COLUMN galaxy_id INT REFERENCES galaxy(galaxy_id) but did not werk

I can not make a copy-paste of the database schema
thanks

Μake the query to add the column firstly and then make another query to add the foreign key to the column you made

It dit not werk, I don’t know if I’making a mistake

because we are not standing next to you, we cannot see what you are doing or the errors or the screen. Please share more information in order to get more help.

I used dit command in the star table

ALTER TABLE star ADD FOREIGN KEY galaxy INT REFERENCES galaxy(galaxy_id);

remember that you need to have the same value for the foreign key name as the column name
(in this case both should be galaxy_id)

so the star table should have a column called galaxy_id
and the galaxy table should have a column called galaxy_id
then you link them together

ALTER TABLE star
    ADD FOREIGN KEY (galaxy_id) REFERENCES galaxy (galaxy_id);

No way that werk for me, I have tried everything

Have you created the columns with the same name?

Please show the schema of each table (the list of columns of each table)



I can’t see enough info from the screenshot.

Please use the mouse to select the output of the /d and /l command and then CTRL+C to copy it and after that CTRL-V to paste it into the forum

(Instructions assume windows operating system)

Edit: also please show the command you used to link the foreign key and the output from this command

ostgres=> \c universe
You are now connected to database "universe" as user "freecodecamp".
universe=> \d star
                                             Table "public.star"
+---------------------+-----------------------+-----------+----------+---------------------------------------+
|       Column        |         Type          | Collation | Nullable |                Default                |
+---------------------+-----------------------+-----------+----------+---------------------------------------+
| star_id             | integer               |           | not null | nextval('star_star_id_seq'::regclass) |
| name                | character varying(20) |           |          |                                       |
| distance_from_earth | integer               |           |          |                                       |
| description         | text                  |           |          |                                       |
| distance            | numeric(4,1)          |           | not null |                                       |
| second_name         | character varying(20) |           |          |                                       |
| galaxy_id           | integer               |           |          |                                       |
+---------------------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "star_pkey" PRIMARY KEY, btree (star_id)
    "star_second_name_key" UNIQUE CONSTRAINT, btree (second_name)
Foreign-key constraints:
    "fkgalaxy" FOREIGN KEY (distance_from_earth) REFERENCES galaxy(galaxy_id)
    "star_galaxy_id_fkey" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
--------------------------------------------------------------------------------------------------------

universe=> \d galaxy
                                                Table "public.galaxy"
+-------------------------+-----------------------+-----------+----------+-------------------------------------------+
|         Column          |         Type          | Collation | Nullable |                  Default                  |
+-------------------------+-----------------------+-----------+----------+-------------------------------------------+
| galaxy_id               | integer               |           | not null | nextval('galaxy_galaxy_id_seq'::regclass) |
| name                    | character varying(20) |           |          |                                           |
| distance_from_earth     | integer               |           |          |                                           |
| description             | text                  |           |          |                                           |
| age_in_million_of_years | numeric(4,1)          |           |          |                                           |
| location                | text                  |           | not null |                                           |
| second_name             | character varying(20) |           |          |                                           |
+-------------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "galaxy_pkey" PRIMARY KEY, btree (galaxy_id)
    "galaxy_second_name_key" UNIQUE CONSTRAINT, btree (second_name)
Referenced by:
    TABLE "star" CONSTRAINT "fkgalaxy" FOREIGN KEY (distance_from_earth) REFERENCES galaxy(galaxy_id)
    TABLE "star" CONSTRAINT "star_galaxy_id_fkey" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
    TABLE "star" CONSTRAINT "star_galaxy_id_fkey1" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)

these are the schema of the table galaxy and star

thanks, i see you have 2 foreign keys referencing galaxy_id from the star table.
Can you remove the distance_from_earth one?
(do you need it for this project?)

yes, I did it again , here the new one

postgres=> \c universe
You are now connected to database "universe" as user "freecodecamp".
universe=> \d star
                                             Table "public.star"
+---------------------+-----------------------+-----------+----------+---------------------------------------+
|       Column        |         Type          | Collation | Nullable |                Default                |
+---------------------+-----------------------+-----------+----------+---------------------------------------+
| star_id             | integer               |           | not null | nextval('star_star_id_seq'::regclass) |
| name                | character varying(20) |           |          |                                       |
| description         | text                  |           |          |                                       |
| distance            | numeric(4,1)          |           | not null |                                       |
| second_name         | character varying(20) |           |          |                                       |
| distance_from_earth | integer               |           |          |                                       |
| galaxy_id           | integer               |           |          |                                       |
+---------------------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "star_pkey" PRIMARY KEY, btree (star_id)
    "star_second_name_key" UNIQUE CONSTRAINT, btree (second_name)
Foreign-key constraints:
    "star_galaxy_id_fkey" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)

universe=> \d galaxy
                                                Table "public.galaxy"
+-------------------------+-----------------------+-----------+----------+-------------------------------------------+
|         Column          |         Type          | Collation | Nullable |                  Default                  |
+-------------------------+-----------------------+-----------+----------+-------------------------------------------+
| galaxy_id               | integer               |           | not null | nextval('galaxy_galaxy_id_seq'::regclass) |
| name                    | character varying(20) |           |          |                                           |
| distance_from_earth     | integer               |           |          |                                           |
| description             | text                  |           |          |                                           |
| age_in_million_of_years | numeric(4,1)          |           |          |                                           |
| location                | text                  |           | not null |                                           |
| second_name             | character varying(20) |           |          |                                           |
+-------------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "galaxy_pkey" PRIMARY KEY, btree (galaxy_id)
    "galaxy_second_name_key" UNIQUE CONSTRAINT, btree (second_name)
Referenced by:
    TABLE "star" CONSTRAINT "star_galaxy_id_fkey" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)

but not pass test:
Each “star” should have a foreign key that references one of the rows in galaxy

the constraint looks correct, do you have any data in the star table right now? You may need to get some in order for the test to pass.

hey hbar1st thank you, I was trying to pass the test without insert any data, now is correct

thank you

1 Like

I always wondered how people doing this course were getting data… Do you just make it up or copy it from somewhere?

now I only used some ficticious data for test, when I get real data I can share it with you

1 Like