Each foreign key column should have the same name as the column it is referencing

Hello!
I have problem with “Each foreign key column should have the same name as the column it is referencing”.
What to do?
Снимок экрана 2023-02-27 175302

universe=> \d galaxy;
                                                  Table "public.galaxy"
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
|           Column           |         Type          | Collation | Nullable |                  Default                  |
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
| name                       | character varying(30) |           | not null |                                           |
| galaxy_id                  | integer               |           | not null | nextval('galaxy_galaxy_id_seq'::regclass) |
| galaxy_types               | character varying(30) |           | not null |                                           |
| galaxy_size                | integer               |           | not null |                                           |
| galaxy_distance_from_earth | integer               |           |          |                                           |
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "galaxy_pkey" PRIMARY KEY, btree (galaxy_id)
    "galaxy_galaxy_id_key" UNIQUE CONSTRAINT, btree (galaxy_id)
Referenced by:
    TABLE "star" CONSTRAINT "fk" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)

universe=> ALTER TABLE galaxy ADD FOREIGN KEY(galaxy_id) REFERENCES galaxy(galaxy_id); 
ALTER TABLE
universe=> \d galaxy;
                                                  Table "public.galaxy"
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
|           Column           |         Type          | Collation | Nullable |                  Default                  |
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
| name                       | character varying(30) |           | not null |                                           |
| galaxy_id                  | integer               |           | not null | nextval('galaxy_galaxy_id_seq'::regclass) |
| galaxy_types               | character varying(30) |           | not null |                                           |
| galaxy_size                | integer               |           | not null |                                           |
| galaxy_distance_from_earth | integer               |           |          |                                           |
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "galaxy_pkey" PRIMARY KEY, btree (galaxy_id)
    "galaxy_galaxy_id_key" UNIQUE CONSTRAINT, btree (galaxy_id)
Foreign-key constraints:
    "galaxy_galaxy_id_fkey" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
Referenced by:
    TABLE "star" CONSTRAINT "fk" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
    TABLE "galaxy" CONSTRAINT "galaxy_galaxy_id_fkey" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)

universe=> \d star;
                                        Table "public.star"
+-----------+-----------------------+-----------+----------+---------------------------------------+
|  Column   |         Type          | Collation | Nullable |                Default                |
+-----------+-----------------------+-----------+----------+---------------------------------------+
| name      | character varying(30) |           | not null |                                       |
| star_id   | integer               |           | not null | nextval('star_star_id_seq'::regclass) |
| star_size | numeric(6,2)          |           |          |                                       |
| star_type | text                  |           |          |                                       |
| galaxy_id | integer               |           | not null |                                       |
+-----------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "star_pkey" PRIMARY KEY, btree (star_id)
    "star_star_id_key" UNIQUE CONSTRAINT, btree (star_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
Referenced by:
    TABLE "planet" CONSTRAINT "fk" FOREIGN KEY (star_id) REFERENCES star(star_id)

universe=> \d planet;
                                              Table "public.planet"
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
|       Column        |         Type          | Collation | Nullable |                  Default                  |
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
| name                | character varying(30) |           | not null |                                           |
| planet_id           | integer               |           | not null | nextval('planet_planet_id_seq'::regclass) |
| planet_type         | character varying(40) |           |          |                                           |
| planet_has_life     | boolean               |           |          |                                           |
| planet_is_spherical | boolean               |           |          |                                           |
| star_id             | integer               |           | not null |                                           |
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "planet_pkey" PRIMARY KEY, btree (planet_id)
    "planet_name_key" UNIQUE CONSTRAINT, btree (name)
    "planet_planet_id_key" UNIQUE CONSTRAINT, btree (planet_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (star_id) REFERENCES star(star_id)
Referenced by:
    TABLE "moon" CONSTRAINT "fk" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)
    TABLE "moon" CONSTRAINT "moon_moon_for_planet_fkey" FOREIGN KEY (moon_for_planet) REFERENCES planet(name)

universe=> \d moon;
                                             Table "public.moon"
+---------------------+-----------------------+-----------+----------+---------------------------------------+
|       Column        |         Type          | Collation | Nullable |                Default                |
+---------------------+-----------------------+-----------+----------+---------------------------------------+
| name                | character varying(30) |           | not null |                                       |
| distance_from_earth | integer               |           | not null |                                       |
| moon_id             | integer               |           | not null | nextval('moon_moon_id_seq'::regclass) |
| moon_for_planet     | character varying(50) |           |          |                                       |
| moon_has_life       | boolean               |           |          |                                       |
| planet_id           | integer               |           | not null |                                       |
+---------------------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "moon_pkey" PRIMARY KEY, btree (moon_id)
    "moon_moon_id_key" UNIQUE CONSTRAINT, btree (moon_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)
    "moon_moon_for_planet_fkey" FOREIGN KEY (moon_for_planet) REFERENCES planet(name)
1 Like

Not exactly sure about your setup and if this is causing a problem, but what is this moon_for_planet?

image

This seems to be a foreign key field that doesn’t have the same name as the field it is referencing.

Thanks for the tip.
I fixed what you said (and others bugs) but it didn’t help. Problem still exists.

universe=> \d
                     List of relations
+--------+----------------------+----------+--------------+
| Schema |         Name         |   Type   |    Owner     |
+--------+----------------------+----------+--------------+
| public | alltable             | table    | freecodecamp |
| 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_planet_id_seq | sequence | freecodecamp |
| public | star                 | table    | freecodecamp |
| public | star_star_id_seq     | sequence | freecodecamp |
+--------+----------------------+----------+--------------+
(9 rows)

universe=> \d alltable
                        Table "public.alltable"
+-------------+-----------------------+-----------+----------+---------+
|   Column    |         Type          | Collation | Nullable | Default |
+-------------+-----------------------+-----------+----------+---------+
| name        | character varying(30) |           | not null |         |
| alltable_id | character varying(30) |           | not null |         |
| galaxy_id   | integer               |           |          |         |
+-------------+-----------------------+-----------+----------+---------+
Indexes:
    "alltable_pkey" PRIMARY KEY, btree (alltable_id)
    "alltable_alltable_id_key" UNIQUE CONSTRAINT, btree (alltable_id)
Referenced by:
    TABLE "galaxy" CONSTRAINT "fk" FOREIGN KEY (alltable_id) REFERENCES alltable(alltable_id)

universe=> \d galaxy
                                                  Table "public.galaxy"
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
|           Column           |         Type          | Collation | Nullable |                  Default                  |
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
| name                       | character varying(30) |           | not null |                                           |
| galaxy_id                  | integer               |           | not null | nextval('galaxy_galaxy_id_seq'::regclass) |
| galaxy_types               | character varying(30) |           | not null |                                           |
| galaxy_size                | integer               |           | not null |                                           |
| galaxy_distance_from_earth | integer               |           |          |                                           |
| alltable_id                | character varying(30) |           |          |                                           |
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "galaxy_pkey" PRIMARY KEY, btree (galaxy_id)
    "galaxy_galaxy_id_key" UNIQUE CONSTRAINT, btree (galaxy_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (alltable_id) REFERENCES alltable(alltable_id)
Referenced by:
    TABLE "star" CONSTRAINT "fk" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)

universe=> \d star
                                        Table "public.star"
+-----------+-----------------------+-----------+----------+---------------------------------------+
|  Column   |         Type          | Collation | Nullable |                Default                |
+-----------+-----------------------+-----------+----------+---------------------------------------+
| name      | character varying(30) |           | not null |                                       |
| star_id   | integer               |           | not null | nextval('star_star_id_seq'::regclass) |
| star_size | numeric(6,2)          |           |          |                                       |
| star_type | text                  |           |          |                                       |
| galaxy_id | integer               |           | not null |                                       |
+-----------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "star_pkey" PRIMARY KEY, btree (star_id)
    "star_star_id_key" UNIQUE CONSTRAINT, btree (star_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
Referenced by:
    TABLE "planet" CONSTRAINT "fk" FOREIGN KEY (star_id) REFERENCES star(star_id)

universe=> \d planet
                                              Table "public.planet"
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
|       Column        |         Type          | Collation | Nullable |                  Default                  |
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
| name                | character varying(30) |           | not null |                                           |
| planet_id           | integer               |           | not null | nextval('planet_planet_id_seq'::regclass) |
| planet_type         | character varying(40) |           |          |                                           |
| planet_has_life     | boolean               |           |          |                                           |
| planet_is_spherical | boolean               |           |          |                                           |
| star_id             | integer               |           | not null |                                           |
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "planet_pkey" PRIMARY KEY, btree (planet_id)
    "planet_planet_id_key" UNIQUE CONSTRAINT, btree (planet_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (star_id) REFERENCES star(star_id)
Referenced by:
    TABLE "moon" CONSTRAINT "fk" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)

universe=> \d star
                                        Table "public.star"
+-----------+-----------------------+-----------+----------+---------------------------------------+
|  Column   |         Type          | Collation | Nullable |                Default                |
+-----------+-----------------------+-----------+----------+---------------------------------------+
| name      | character varying(30) |           | not null |                                       |
| star_id   | integer               |           | not null | nextval('star_star_id_seq'::regclass) |
| star_size | numeric(6,2)          |           |          |                                       |
| star_type | text                  |           |          |                                       |
| galaxy_id | integer               |           | not null |                                       |
+-----------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "star_pkey" PRIMARY KEY, btree (star_id)
    "star_star_id_key" UNIQUE CONSTRAINT, btree (star_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
Referenced by:
    TABLE "planet" CONSTRAINT "fk" FOREIGN KEY (star_id) REFERENCES star(star_id)

Do you have any other solutions?

You’re still getting the same foreign key error, or a new error now?

I don’t see any other foreign keys that are named incorrectly. What exactly is the alltable table???

This shouldn’t cause a primary key naming error, but your alltable_id is listed as the alltable primary key, but it is a VARCHAR(30) field. One of the restrictions was that all primary keys be auto-incrementing… not sure how you can auto-increment a text field.

Per your output:

universe=> \d alltable
| alltable_id | character varying(30) |           | not null |         |

Indexes:
    "alltable_pkey" PRIMARY KEY, btree (alltable_id)

Per the requirements list:
image

Further indication… your alltable table doesn’t have an alltable_id_seq sequence generator:
image

I would think for this all primary keys would need to be serial so it can auto-increment. If you are getting new errors you need to share those if you’re having continuing errors, or we don’t know what to look for.

Oh, and I noticed that you forgot to provide the output for the moon table… you listed star twice.

Also, I set up a sample database setup the way yours is, and even with the VARCHAR as the primary key, it did not thrown an error regarding auto-incrementing so I’m guessing they don’t check that too strictly, but also, the test database passed the primary-key naming requirements, so unless there is something wrong in your moon table, it should pass.

@kinome79
Hello! :wave:
Thanks for your help!
I only have one error.

Each foreign key column should have the same name as the column it is referencing

All other items have been completed.
I have also changed my tables but it does not help.

Each foreign key column should have the same name as the column it is referencing

Please explain to me that in this paragraph I need to do what columns they mean?

universe=> \d
                     List of relations
+--------+----------------------+----------+--------------+
| Schema |         Name         |   Type   |    Owner     |
+--------+----------------------+----------+--------------+
| public | alltable             | table    | freecodecamp |
| 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_planet_id_seq | sequence | freecodecamp |
| public | star                 | table    | freecodecamp |
| public | star_star_id_seq     | sequence | freecodecamp |
+--------+----------------------+----------+--------------+
(9 rows)

universe=> \d alltable
                        Table "public.alltable"
+-------------+-----------------------+-----------+----------+---------+
|   Column    |         Type          | Collation | Nullable | Default |
+-------------+-----------------------+-----------+----------+---------+
| name        | character varying(30) |           | not null |         |
| alltable_id | character varying(30) |           | not null |         |
| galaxy_id   | integer               |           |          |         |
| moon_id     | integer               |           |          |         |
+-------------+-----------------------+-----------+----------+---------+
Indexes:
    "alltable_pkey" PRIMARY KEY, btree (alltable_id)
    "alltable_alltable_id_key" UNIQUE CONSTRAINT, btree (alltable_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (moon_id) REFERENCES moon(moon_id)
Referenced by:
    TABLE "galaxy" CONSTRAINT "fk" FOREIGN KEY (alltable_id) REFERENCES alltable(alltable_id)

universe=> \d galaxy
                                                  Table "public.galaxy"
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
|           Column           |         Type          | Collation | Nullable |                  Default                  |
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
| name                       | character varying(30) |           | not null |                                           |
| galaxy_id                  | integer               |           | not null | nextval('galaxy_galaxy_id_seq'::regclass) |
| galaxy_types               | character varying(30) |           | not null |                                           |
| galaxy_size                | integer               |           | not null |                                           |
| galaxy_distance_from_earth | integer               |           |          |                                           |
| alltable_id                | character varying(30) |           |          |                                           |
+----------------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "galaxy_pkey" PRIMARY KEY, btree (galaxy_id)
    "galaxy_galaxy_id_key" UNIQUE CONSTRAINT, btree (galaxy_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (alltable_id) REFERENCES alltable(alltable_id)
Referenced by:
    TABLE "star" CONSTRAINT "fk" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)

universe=> \d star
                                        Table "public.star"
+-----------+-----------------------+-----------+----------+---------------------------------------+
|  Column   |         Type          | Collation | Nullable |                Default                |
+-----------+-----------------------+-----------+----------+---------------------------------------+
| name      | character varying(30) |           | not null |                                       |
| star_id   | integer               |           | not null | nextval('star_star_id_seq'::regclass) |
| star_size | numeric(6,2)          |           |          |                                       |
| star_type | text                  |           |          |                                       |
| galaxy_id | integer               |           | not null |                                       |
+-----------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "star_pkey" PRIMARY KEY, btree (star_id)
    "star_star_id_key" UNIQUE CONSTRAINT, btree (star_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
Referenced by:
    TABLE "planet" CONSTRAINT "fk" FOREIGN KEY (star_id) REFERENCES star(star_id)

universe=> \d planet
                                              Table "public.planet"
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
|       Column        |         Type          | Collation | Nullable |                  Default                  |
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
| name                | character varying(30) |           | not null |                                           |
| planet_id           | integer               |           | not null | nextval('planet_planet_id_seq'::regclass) |
| planet_type         | character varying(40) |           |          |                                           |
| planet_has_life     | boolean               |           |          |                                           |
| planet_is_spherical | boolean               |           |          |                                           |
| star_id             | integer               |           | not null |                                           |
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "planet_pkey" PRIMARY KEY, btree (planet_id)
    "planet_planet_id_key" UNIQUE CONSTRAINT, btree (planet_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (star_id) REFERENCES star(star_id)
Referenced by:
    TABLE "moon" CONSTRAINT "fk" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)

universe=> \d moon
                                             Table "public.moon"
+---------------------+-----------------------+-----------+----------+---------------------------------------+
|       Column        |         Type          | Collation | Nullable |                Default                |
+---------------------+-----------------------+-----------+----------+---------------------------------------+
| name                | character varying(30) |           | not null |                                       |
| distance_from_earth | integer               |           | not null |                                       |
| moon_id             | integer               |           | not null | nextval('moon_moon_id_seq'::regclass) |
| moon_for_planet     | character varying(50) |           |          |                                       |
| moon_has_life       | boolean               |           |          |                                       |
| planet_id           | integer               |           | not null |                                       |
+---------------------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "moon_pkey" PRIMARY KEY, btree (moon_id)
    "moon_moon_id_key" UNIQUE CONSTRAINT, btree (moon_id)
Foreign-key constraints:
    "fk" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)
Referenced by:
    TABLE "alltable" CONSTRAINT "fk" FOREIGN KEY (moon_id) REFERENCES moon(moon_id)

Hello! :wave:
Thanks for the help!
I managed. :grinning:

The problem was that I had the title:

foreign-key constraints:
     "fk" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)

but it was necessary

foreign-key constraints:
“fk_planet” FOREIGN KEY (planet_id) REFERENCES planet(planet_id)

Glad to hear you got it worked out. I noticed that your title was “fk” where as anytime I set a foreign key it had a title of “fk_

_” but I’m surprised to hear that affected that tests. Good job.