Celestial Bodies Database - Primary Keys Issue

I’m having an issue with this project. It looks like I have created all the primary keys with the correct naming convention, but even though I do have a primary key for every table (at the very least), the only tasks that won’t flag complete are:

  • Each table should have a primary key
  • Each primary key column should follow the naming convention table_name_id . For example, the moon table should have a primary key column named moon_id

Please see the dump of my code below. I used strong tags on the ALTER statements further down creating the primary keys. I appreciate any help. Thanks!

CREATE TABLE public.asteroid (
    asteroid_id integer NOT NULL,
    name character varying(30) NOT NULL,
    revolves_around text,
    distance_from_earth integer,
    danger boolean
);


ALTER TABLE public.asteroid OWNER TO freecodecamp;

--
-- Name: asteroid_asteroid_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.asteroid_asteroid_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.asteroid_asteroid_id_seq OWNER TO freecodecamp;

--
-- Name: asteroid_asteroid_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.asteroid_asteroid_id_seq OWNED BY public.asteroid.asteroid_id;


--
-- Name: galaxy; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.galaxy (
    galaxy_id integer NOT NULL,
    name character varying(30) NOT NULL,
    description text,
    fourth_column integer,
    fifth_column boolean
);


ALTER TABLE public.galaxy OWNER TO freecodecamp;

--
-- Name: galaxy_galaxy_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.galaxy_galaxy_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.galaxy_galaxy_id_seq OWNER TO freecodecamp;

--
-- Name: galaxy_galaxy_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.galaxy_galaxy_id_seq OWNED BY public.galaxy.galaxy_id;


--
-- Name: moon; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.moon (
    name character varying(30) NOT NULL,
    planet_id integer,
    moon_id integer NOT NULL,
    fourth_column boolean,
    fifth_column integer
);


ALTER TABLE public.moon OWNER TO freecodecamp;

--
-- Name: moon_moon_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.moon_moon_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.moon_moon_id_seq OWNER TO freecodecamp;

--
-- Name: moon_moon_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.moon_moon_id_seq OWNED BY public.moon.moon_id;


--
-- Name: planet; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.planet (
    planet_id integer NOT NULL,
    name character varying(30) NOT NULL,
    has_life boolean,
    is_spherical boolean,
    age_in_millions_of_years integer,
    distance_from_earth numeric(8,2),
    description text,
    year_discovered integer,
    star_id integer
);


ALTER TABLE public.planet OWNER TO freecodecamp;

--
-- Name: planet_planet_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.planet_planet_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.planet_planet_id_seq OWNER TO freecodecamp;

--
-- Name: planet_planet_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.planet_planet_id_seq OWNED BY public.planet.planet_id;


--
-- Name: star; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.star (
    star_id integer NOT NULL,
    name character varying(30) NOT NULL,
    galaxy_id integer,
    fourth_column integer,
    fifth_column text
);


ALTER TABLE public.star OWNER TO freecodecamp;

--
-- Name: star_star_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.star_star_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.star_star_id_seq OWNER TO freecodecamp;

--
-- Name: star_star_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.star_star_id_seq OWNED BY public.star.star_id;


--
-- Name: asteroid asteroid_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.asteroid ALTER COLUMN asteroid_id SET DEFAULT nextval('public.asteroid_asteroid_id_seq'::regclass);


--
-- Name: galaxy galaxy_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.galaxy ALTER COLUMN galaxy_id SET DEFAULT nextval('public.galaxy_galaxy_id_seq'::regclass);


--
-- Name: moon moon_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.moon ALTER COLUMN moon_id SET DEFAULT nextval('public.moon_moon_id_seq'::regclass);


--
-- Name: planet planet_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.planet ALTER COLUMN planet_id SET DEFAULT nextval('public.planet_planet_id_seq'::regclass);


--
-- Name: star star_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.star ALTER COLUMN star_id SET DEFAULT nextval('public.star_star_id_seq'::regclass);


--
-- Data for Name: asteroid; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.asteroid VALUES (1, 'Asteroid1', NULL, NULL, NULL);
INSERT INTO public.asteroid VALUES (2, 'Asteroid2', NULL, NULL, NULL);
INSERT INTO public.asteroid VALUES (3, 'Asteroid3', NULL, NULL, NULL);


--
-- Data for Name: galaxy; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.galaxy VALUES (1, 'Milky Way', NULL, NULL, NULL);
INSERT INTO public.galaxy VALUES (2, 'Andromeda', NULL, NULL, NULL);
INSERT INTO public.galaxy VALUES (3, 'Comet', NULL, NULL, NULL);
INSERT INTO public.galaxy VALUES (4, 'Galaxy4', NULL, NULL, NULL);
INSERT INTO public.galaxy VALUES (5, 'Galaxy5', NULL, NULL, NULL);
INSERT INTO public.galaxy VALUES (6, 'Galaxy6', NULL, NULL, NULL);


--
-- Data for Name: moon; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.moon VALUES ('Luna', 1, 1, NULL, NULL);
INSERT INTO public.moon VALUES ('Europa', NULL, 2, NULL, NULL);
INSERT INTO public.moon VALUES ('Callisto', NULL, 3, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon4', NULL, 4, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon5', NULL, 5, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon6', NULL, 6, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon7', NULL, 7, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon8', NULL, 8, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon9', NULL, 9, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon10', NULL, 10, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon11', NULL, 11, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon12', NULL, 12, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon13', NULL, 13, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon14', NULL, 14, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon15', NULL, 15, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon16', NULL, 16, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon17', NULL, 17, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon18', NULL, 18, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon19', NULL, 19, NULL, NULL);
INSERT INTO public.moon VALUES ('Moon20', NULL, 20, NULL, NULL);


--
-- Data for Name: planet; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.planet VALUES (1, 'Earth', true, true, NULL, NULL, NULL, NULL, 1);
INSERT INTO public.planet VALUES (2, 'Mercury', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO public.planet VALUES (3, 'Venus', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO public.planet VALUES (4, 'Mars', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO public.planet VALUES (5, 'Jupiter', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO public.planet VALUES (6, 'Saturn', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO public.planet VALUES (7, 'Uranus', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO public.planet VALUES (8, 'Neptune', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO public.planet VALUES (9, 'Pluto', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO public.planet VALUES (10, 'Planet 10', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO public.planet VALUES (11, 'Planet 11', NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO public.planet VALUES (12, 'Planet 12', NULL, NULL, NULL, NULL, NULL, NULL, NULL);


--
-- Data for Name: star; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.star VALUES (1, 'Polaris', 1, NULL, NULL);
INSERT INTO public.star VALUES (2, 'Star2', NULL, NULL, NULL);
INSERT INTO public.star VALUES (3, 'Star3', NULL, NULL, NULL);
INSERT INTO public.star VALUES (4, 'Star4', NULL, NULL, NULL);
INSERT INTO public.star VALUES (5, 'Star5', NULL, NULL, NULL);
INSERT INTO public.star VALUES (6, 'Star6', NULL, NULL, NULL);


--
-- Name: asteroid_asteroid_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.asteroid_asteroid_id_seq', 1, false);


--
-- Name: galaxy_galaxy_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.galaxy_galaxy_id_seq', 1, false);


--
-- Name: moon_moon_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.moon_moon_id_seq', 1, false);


--
-- Name: planet_planet_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.planet_planet_id_seq', 1, false);


--
-- Name: star_star_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.star_star_id_seq', 1, false);


--
-- Name: asteroid asteroid_id; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

**ALTER TABLE ONLY public.asteroid**
**    ADD CONSTRAINT asteroid_id PRIMARY KEY (asteroid_id);**


--
-- Name: asteroid asteroid_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.asteroid
    ADD CONSTRAINT asteroid_name_key UNIQUE (name);


--
-- Name: galaxy galaxy_galaxy_id_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.galaxy
    ADD CONSTRAINT galaxy_galaxy_id_key UNIQUE (galaxy_id);


--
-- Name: galaxy galaxy_id; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

**ALTER TABLE ONLY public.galaxy**
**    ADD CONSTRAINT galaxy_id PRIMARY KEY (galaxy_id);**


--
-- Name: moon moon_id; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

**ALTER TABLE ONLY public.moon**
**    ADD CONSTRAINT moon_id PRIMARY KEY (moon_id);**


--
-- Name: moon moon_moon_id_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT moon_moon_id_key UNIQUE (moon_id);


--
-- Name: planet planet_id; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

**ALTER TABLE ONLY public.planet**
**    ADD CONSTRAINT planet_id PRIMARY KEY (planet_id);**


--
-- Name: star star_id; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

**ALTER TABLE ONLY public.star**
**    ADD CONSTRAINT star_id PRIMARY KEY (star_id);**


--
-- Name: galaxy uq_galaxy_id; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.galaxy
    ADD CONSTRAINT uq_galaxy_id UNIQUE (galaxy_id);


--
-- Name: planet uq_planet_id; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT uq_planet_id UNIQUE (planet_id);


--
-- Name: star uq_star_id; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.star
    ADD CONSTRAINT uq_star_id UNIQUE (star_id);


--
-- Name: moon fkey_planet_id; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT fkey_planet_id FOREIGN KEY (planet_id) REFERENCES public.planet(planet_id);


--
-- Name: planet fkey_star_id; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT fkey_star_id FOREIGN KEY (star_id) REFERENCES public.star(star_id);


--
-- Name: star galaxy_id; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.star
    ADD CONSTRAINT galaxy_id FOREIGN KEY (galaxy_id) REFERENCES public.galaxy(galaxy_id);


--
-- PostgreSQL database dump complete

Would it be possible to see a \l and \d (a list of all your tables)
And then the output of running \d on each table listed?

Absolutely. Thanks for your response.

\d : 
                       List of relations
+--------+--------------------------+----------+--------------+
| Schema |           Name           |   Type   |    Owner     |
+--------+--------------------------+----------+--------------+
| public | asteroid                 | table    | freecodecamp |
| public | asteroid_asteroid_id_seq | sequence | 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 |
+--------+--------------------------+----------+--------------+
\l: 
                                 List of databases
+-----------+--------------+----------+---------+---------+-----------------------+
|   Name    |    Owner     | Encoding | Collate |  Ctype  |   Access privileges   |
+-----------+--------------+----------+---------+---------+-----------------------+
| postgres  | postgres     | UTF8     | C.UTF-8 | C.UTF-8 |                       |
| template0 | postgres     | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +|
|           |              |          |         |         | postgres=CTc/postgres |
| template1 | postgres     | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +|
|           |              |          |         |         | postgres=CTc/postgres |
| universe  | freecodecamp | UTF8     | C.UTF-8 | C.UTF-8 |                       |
+-----------+--------------+----------+---------+---------+-----------------------+
\d table-level: 
                                               Table "public.asteroid"
+---------------------+-----------------------+-----------+----------+-----------------------------------------------+
|       Column        |         Type          | Collation | Nullable |                    Default                    |
+---------------------+-----------------------+-----------+----------+-----------------------------------------------+
| asteroid_id         | integer               |           | not null | nextval('asteroid_asteroid_id_seq'::regclass) |
| name                | character varying(30) |           | not null |                                               |
| revolves_around     | text                  |           |          |                                               |
| distance_from_earth | integer               |           |          |                                               |
| danger              | boolean               |           |          |                                               |
+---------------------+-----------------------+-----------+----------+-----------------------------------------------+
Indexes:
    "asteroid_id" PRIMARY KEY, btree (asteroid_id)
    "asteroid_name_key" UNIQUE CONSTRAINT, btree (name)

                                           Table "public.galaxy"
+---------------+-----------------------+-----------+----------+-------------------------------------------+
|    Column     |         Type          | Collation | Nullable |                  Default                  |
+---------------+-----------------------+-----------+----------+-------------------------------------------+
| galaxy_id     | integer               |           | not null | nextval('galaxy_galaxy_id_seq'::regclass) |
| name          | character varying(30) |           | not null |                                           |
| description   | text                  |           |          |                                           |
| fourth_column | integer               |           |          |                                           |
| fifth_column  | boolean               |           |          |                                           |
+---------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "galaxy_id" PRIMARY KEY, btree (galaxy_id)
    "galaxy_galaxy_id_key" UNIQUE CONSTRAINT, btree (galaxy_id)
    "uq_galaxy_id" UNIQUE CONSTRAINT, btree (galaxy_id)
Referenced by:
    TABLE "star" CONSTRAINT "galaxy_id" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)

                                          Table "public.moon"
+---------------+-----------------------+-----------+----------+---------------------------------------+
|    Column     |         Type          | Collation | Nullable |                Default                |
+---------------+-----------------------+-----------+----------+---------------------------------------+
| name          | character varying(30) |           | not null |                                       |
| planet_id     | integer               |           |          |                                       |
| moon_id       | integer               |           | not null | nextval('moon_moon_id_seq'::regclass) |
| fourth_column | boolean               |           |          |                                       |
| fifth_column  | integer               |           |          |                                       |
+---------------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "moon_id" PRIMARY KEY, btree (moon_id)
    "moon_moon_id_key" UNIQUE CONSTRAINT, btree (moon_id)
Foreign-key constraints:
    "fkey_planet_id" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)

                                                 Table "public.planet"
+--------------------------+-----------------------+-----------+----------+-------------------------------------------+
|          Column          |         Type          | Collation | Nullable |                  Default                  |
+--------------------------+-----------------------+-----------+----------+-------------------------------------------+
| planet_id                | integer               |           | not null | nextval('planet_planet_id_seq'::regclass) |
| name                     | character varying(30) |           | not null |                                           |
| has_life                 | boolean               |           |          |                                           |
| is_spherical             | boolean               |           |          |                                           |
| age_in_millions_of_years | integer               |           |          |                                           |
| distance_from_earth      | numeric(8,2)          |           |          |                                           |
| description              | text                  |           |          |                                           |
| year_discovered          | integer               |           |          |                                           |
| star_id                  | integer               |           |          |                                           |
+--------------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "planet_id" PRIMARY KEY, btree (planet_id)
    "uq_planet_id" UNIQUE CONSTRAINT, btree (planet_id)
Foreign-key constraints:
    "fkey_star_id" FOREIGN KEY (star_id) REFERENCES star(star_id)
Referenced by:
    TABLE "moon" CONSTRAINT "fkey_planet_id" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)

                                          Table "public.star"
+---------------+-----------------------+-----------+----------+---------------------------------------+
|    Column     |         Type          | Collation | Nullable |                Default                |
+---------------+-----------------------+-----------+----------+---------------------------------------+
| star_id       | integer               |           | not null | nextval('star_star_id_seq'::regclass) |
| name          | character varying(30) |           | not null |                                       |
| galaxy_id     | integer               |           |          |                                       |
| fourth_column | integer               |           |          |                                       |
| fifth_column  | text                  |           |          |                                       |
+---------------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "star_id" PRIMARY KEY, btree (star_id)
    "uq_star_id" UNIQUE CONSTRAINT, btree (star_id)
Foreign-key constraints:
    "galaxy_id" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
Referenced by:
    TABLE "planet" CONSTRAINT "fkey_star_id" FOREIGN KEY (star_id) REFERENCES star(star_id)

the primary ids look good to me. Do you have some rows in each table?

@moT01 Hi Tom, if you can, pls take at this issue and suggest what can be done. The dump is provided and the tables look good.

Yes, I do. Mostly it’s the amount that it calls for in the instructions.

It’s got me pretty confused, to say the least. Here’s a couple screenshots showing all the other tasks completed, but these two - for some reason - still incomplete.

Thanks for your continued help!


I’m not quite sure exactly what is going on here @tbooth21. I ran a query similar to what the test runs and I get output that looks like this:
Screen Shot 2023-01-31 at 12.11.58 PM

I think it’s cause the galaxy_id foreign key on the star table has a constraint name of galaxy_id, which is the name of a primary key - all the other constraints are named something else, e.g. fkey_star_id.

Rename that constraint and I think it should pass. Let us know if it works.

1 Like

You were exactly right. As soon as I renamed it, the tutorial showed 100% complete.

Thanks for your help, @moT01 and @hbar1st !

2 Likes