Celestial Bodies Database - foreign key same name

Hi,
I could tick all boxes of the Celestial Bodies Database project but one- the last one “Each foreign key column should have the same name as the column it is referencing” and I do not understand why. The foreign key column in my star database is called " galaxy_id" as in the reference table galaxy where the referred column is “galaxy_id”. Same principle for foreign key in planet and moon tables. Do I misunderstand the task?
Thank you!

From your description that sounds correct. Could you post dump of your database so it could be looked at?

Thank you for your reply!

The corresponding code is:

ALTER TABLE ONLY public.star ADD CONSTRAINT fk FOREIGN KEY (galaxy_id) REFERENCES public.galaxy(galaxy_id);
ALTER TABLE ONLY public.planet ADD CONSTRAINT fk FOREIGN KEY (star_id) REFERENCES public.star(star_id);
ALTER TABLE ONLY public.moon ADD CONSTRAINT fk FOREIGN KEY (planet_id) REFERENCES public.planet(planet_id);

The test checks all the foreign keys in your database. Is there any others that don’t follow this naming convention @wahrwahrnehmung?

I double-checked but no, this is not the case:
image
I’ve only one other table [rockets] and this one does not have a foreign key.

For instance, this is how the details of my star table looks like

Same principle for planet and moon table

Pretty sure it’s just a glitch, but why don’t any of them have underscores?

Not that I don’t believe you, but I feel like there might be a typo or something in one of them. Can you show me the output of this command: SELECT tc.table_schema, tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY';

That’s what the test uses, it should bring up all the foreign keys and the columns they reference.

Edit: Are you sure that rockets one is correct? In the first image there, all the sequences show <name> id <name> seq - but the rockets has <name> <name> id seq

Hi,
Underscores: the missing underscores seem just an issue in displaying - now they are there.

Rockets_id: I renamed the id variable only afterwards, maybe that is the reason why. Anyways, I dropped the rockets table ( last requirement is is still not ticked) and created it again but no changes.


Thank you so much for your help!

Hmm, yea - I see the problem…

The user story says, “Each foreign key column should have the same name as the column it is referencing”

Each entry in the foreign_column_name (foreign key column) is supposed to match the entry in the column_name (referenced column). The first three items in the image are galaxy_id, star_id, and planet_id - they all reference galaxy_id. So the names don’t align. Perhaps that’s some poor design on my part - I didn’t think about someone wanting to reference the same column more than once. For now, the only way you will be able to get it to pass is if you align those names - which probably means removing a lot of your foreign keys. Sorry.

Edit: Just for some more context - the way I had intended this to be implemented was to have each table have one foreign key that references the thing bigger than it. For example, the star table would have only a galaxy_id foreign key because each star belongs to a galaxy. The planet table would have only a star_id foreign key because each planet has a host star, etc.

Okay, that’s how I interpreted the table as well. Thanks for the explanation - this was actually my intention. Nevertheless, I repeated the process of setting the foreign key constraint several times and this is exactly what happens when running

ALTER TABLE ONLY public.star ADD CONSTRAINT fk FOREIGN KEY (galaxy_id) REFERENCES public.galaxy(galaxy_id);
ALTER TABLE ONLY public.planet ADD CONSTRAINT fk FOREIGN KEY (star_id) REFERENCES public.star(star_id);
ALTER TABLE ONLY public.moon ADD CONSTRAINT fk FOREIGN KEY (planet_id) REFERENCES public.planet(planet_id);

I built my database once more from scratch but this time I implemented the foreign key already when creating the tables. Now it works.
I’m still not sure why the code above leads to multiple foreign keys.

Thank you so much, though! Very happy I finally did it. :slight_smile:

1 Like

Looking back at some of your previous images, I don’t see all those extra keys. Perhaps that query the test is using isn’t quite doing what I think it’s doing. I may have to give it a try and take another look.

I have a similar problem when doing the tests and can’t figure out why.
I’m setting FK for each table to the bigger ones (i imagined it was what was intended) but it’s not getting checked when running the coderoad check.
¿Is there something i’m missing?
I leave the details of the star table.

Edit: Found the solution myself. I’ll leave it here in case anyone get’s stuck on the same loop. I haven’t introduced any rows so the system was correctly checking that no row was referenced, even thought the columns were correctly referenced. Just insert some data and the items will get checked.

2 Likes

If you share a dump of your database using the command in the instructions, I can take a look and see if I can find any problems @Santiago-Motter. I would put the file online somewhere and share a link to it so it doesn’t take up a bunch of space on here.

1 Like

Hi! I had the same problem with the foreign keys. In my case it seems like the problem were the constraint names: i used the same name to all the constraints, like you, and that led me to multiple foreign keys.
I solved it by rebuilding my database and using unique constraint names for my foreign keys like: “fk_planet”, “fk_star”, etc.
Example of foreign keys for moon and planet tables:

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT fk_planet FOREIGN KEY (planet_id) REFERENCES public.planet(planet_id);
ALTER TABLE ONLY public.planet
    ADD CONSTRAINT fk_star FOREIGN KEY (star_id) REFERENCES public.star(star_id);

I hope this helps anyone in the future :smiley:

THANKS A LOT FOR THIS !!! I double triple confirm this was my case too! I had the same constraint name for all tables.

Not only this… I was also having issue with the INT task and the moment I solved the constraint one it popped the CONGRATS message!

I agree. I wasn’t getting green check marks after creating my foreign keys. It was until after I inserted values onto the rows(records) in my star table that the requirement was met.