Celestial Bodies Database Primary key isn't recognized

Although I have added primary keys to each of my 5 tables in the universe database for this project; I still cannot get past the task “each table should have a primary key”. I’ve checked each table and they all have a primary key starting with the name of the table and followed by “_id” as the task requires. However, it still will not register. Please help me I should’ve been done with this project 1 week ago.

1 Like

we are not able to help without your code.

are you able to provide the code for your project?
in this case maybe a database dump

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)
-- Dumped by pg_dump version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE universe;
--
-- Name: universe; Type: DATABASE; Schema: -; Owner: freecodecamp
--

CREATE DATABASE universe WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8';


ALTER DATABASE universe OWNER TO freecodecamp;

\connect universe

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

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

CREATE TABLE public.galaxy (
    name character varying(30) NOT NULL,
    galaxy_id integer NOT NULL,
    galaxy_types character varying(50) NOT NULL,
    dwarf_galaxy boolean NOT NULL
);


ALTER TABLE public.galaxy OWNER TO freecodecamp;

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

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


ALTER TABLE public.galaxy_id_seq OWNER TO freecodecamp;

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

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


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

CREATE TABLE public.life (
    life_id character varying(30) NOT NULL,
    name character varying(30),
    how_many integer NOT NULL,
    life_expectancy integer
);


ALTER TABLE public.life OWNER TO freecodecamp;

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

CREATE TABLE public.moon (
    moon_id integer NOT NULL,
    moon_description text,
    moon_diameter integer,
    moon_name character varying(30) NOT NULL,
    name character varying(30)
);


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 (
    name character varying(30) NOT NULL,
    climate character varying(30) NOT NULL,
    age_in_millions_of_years numeric NOT NULL,
    planet_id integer NOT NULL,
    distance_from_earth numeric,
    number_of_moons 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 bigint NOT NULL,
    in_constellation boolean NOT NULL,
    distance_from_earth numeric,
    galaxy_id integer NOT NULL,
    name character varying(30)
);


ALTER TABLE public.star OWNER TO freecodecamp;

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

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


ALTER TABLE public.star_galaxy_id_seq OWNER TO freecodecamp;

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

ALTER SEQUENCE public.star_galaxy_id_seq OWNED BY public.star.galaxy_id;


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

CREATE SEQUENCE public.star_star_id_seq
    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: galaxy galaxy_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.galaxy ALTER COLUMN galaxy_id SET DEFAULT nextval('public.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);


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

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


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

INSERT INTO public.galaxy VALUES ('milkyway', 1, 'spiral', true);
INSERT INTO public.galaxy VALUES ('andromeda', 2, 'spiral', true);
INSERT INTO public.galaxy VALUES ('magellaniccloud', 3, 'irregular', false);
INSERT INTO public.galaxy VALUES ('hoag', 4, 'ring', false);
INSERT INTO public.galaxy VALUES ('messier', 5, 'spiral', false);
INSERT INTO public.galaxy VALUES ('centaurus', 6, 'elliptical', true);


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

INSERT INTO public.life VALUES ('1', 'living_organisms', 1000000, 120);
INSERT INTO public.life VALUES ('2', 'humans', 10000000, 110);
INSERT INTO public.life VALUES ('3', 'bugs', 10000000, 200);


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

INSERT INTO public.moon VALUES (1, 'controls_the_earths_ocean', 2159, 'luna', 'earth');
INSERT INTO public.moon VALUES (2, 'greek_word_means_flight', 14, 'phobos', 'mars');
INSERT INTO public.moon VALUES (3, 'second_mar_moon', 7, 'deimos', 'mars');
INSERT INTO public.moon VALUES (4, 'discovered_by_galileo', 2263, 'io', 'jupiter');
INSERT INTO public.moon VALUES (5, 'greek_name_coming_from_daughter_of_agenor', 1938, 'europa', 'jupiter');
INSERT INTO public.moon VALUES (6, 'carried_olympus_by_jupiter_disguised_as_eagle', 3272, 'ganymede', 'jupiter');
INSERT INTO public.moon VALUES (7, 'daughter_of_lycaon', 2994, 'callisto', 'jupiter');
INSERT INTO public.moon VALUES (8, 'found_by_ee_bernard', 102, 'amalthea', 'jupiter');
INSERT INTO public.moon VALUES (9, 'named_after_giant_in_greek_lit', 246, 'mimas', 'saturn');
INSERT INTO public.moon VALUES (10, 'named_by_founder_herschels_son', 312, 'enceladus', 'saturn');
INSERT INTO public.moon VALUES (11, 'discovered_by_gd_cassini', 658, 'tethys', 'saturn');
INSERT INTO public.moon VALUES (12, 'first_saturn_moon_found_by_huygens', 3198, 'titan', 'saturn');
INSERT INTO public.moon VALUES (13, 'sister_of_cronos', 696, 'dione', 'saturn');
INSERT INTO public.moon VALUES (14, 'mother_of_zeus', 948, 'rhea', 'saturn');
INSERT INTO public.moon VALUES (15, 'named_for_a_malevolent_spirit_in_popes_rape_of_locke', 706, 'umbriel', 'uranus');
INSERT INTO public.moon VALUES (16, 'discovered_by_lassell_but_named_by_herschel', 718, 'ariel', 'uranus');
INSERT INTO public.moon VALUES (17, 'discovered_by_gp_kuiper', 210, 'nereid', 'neptune');
INSERT INTO public.moon VALUES (18, 'discovered_by_lassell_named_after_son_of_poseidon', 1680, 'triton', 'neptune');
INSERT INTO public.moon VALUES (19, 'discovered_by_cw_tombaugh', 752, 'charon', 'pluto');
INSERT INTO public.moon VALUES (20, 'discovered_by_jw_christy', 700, 'nix', 'pluto');


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

INSERT INTO public.planet VALUES ('earth', 'varies_between_14c_and_56C', 50, 1, 0, 1);
INSERT INTO public.planet VALUES ('mars', 'about_neg_63C', 46, 12, 50000, 2);
INSERT INTO public.planet VALUES ('mercury', 'scorching_up_to_430C', 49, 2, 25000, 0);
INSERT INTO public.planet VALUES ('venus', 'scorching_up_to_465C', 51, 3, 25000, 0);
INSERT INTO public.planet VALUES ('ceres', 'neg_100C', 45, 4, 70000, 1);
INSERT INTO public.planet VALUES ('saturn', 'neg_138C', 50, 6, 70000, 53);
INSERT INTO public.planet VALUES ('jupiter', 'neg_108C', 46, 5, 70000, 53);
INSERT INTO public.planet VALUES ('uranus', 'neg_195C', 51, 7, 80000, 27);
INSERT INTO public.planet VALUES ('neptune', 'neg_201C', 51, 8, 80000, 14);
INSERT INTO public.planet VALUES ('pluto', 'neg_387C', 48, 9, 100000, 5);
INSERT INTO public.planet VALUES ('eris', 'neg_243C', 46, 10, 100000, 1);
INSERT INTO public.planet VALUES ('makemake', 'neg_289C', 47, 11, 100000, 0);


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

INSERT INTO public.star VALUES (1, false, 50000, 1, 'sun');
INSERT INTO public.star VALUES (2, true, 0, 2, 'beta');
INSERT INTO public.star VALUES (3, true, 25000, 3, 'gamma');
INSERT INTO public.star VALUES (4, true, 70000, 4, 'delta');
INSERT INTO public.star VALUES (5, true, 80000, 5, 'epsilon');
INSERT INTO public.star VALUES (6, false, 100000, 6, 'zeta');


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

SELECT pg_catalog.setval('public.galaxy_id_seq', 6, true);


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

SELECT pg_catalog.setval('public.moon_moon_id_seq', 5, true);


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

SELECT pg_catalog.setval('public.planet_planet_id_seq', 14, true);


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

SELECT pg_catalog.setval('public.star_galaxy_id_seq', 10, true);


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

SELECT pg_catalog.setval('public.star_star_id_seq', 4, true);


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

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


--
-- Name: life life_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.life
    ADD CONSTRAINT life_name_key UNIQUE (name);


--
-- Name: life life_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.life
    ADD CONSTRAINT life_pkey PRIMARY KEY (life_id);


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

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT moon_moon_name_key UNIQUE (moon_name);


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

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


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

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT planet_name_key UNIQUE (name);


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

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT planet_pkey PRIMARY KEY (planet_id, name);


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

ALTER TABLE ONLY public.star
    ADD CONSTRAINT star_distance_from_earth_key UNIQUE (distance_from_earth);


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

ALTER TABLE ONLY public.star
    ADD CONSTRAINT star_name_key UNIQUE (name);


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

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


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

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT moon_name_fkey FOREIGN KEY (name) REFERENCES public.planet(name);


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

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT planet_distance_from_earth_fkey FOREIGN KEY (distance_from_earth) REFERENCES public.star(distance_from_earth);


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

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


--
-- PostgreSQL database dump complete
--

this one has a primary key on two columns

So I’m assuming I’m not allowed to use composite primary keys

The tests don’t allow it, you need to have only the id column as primary key

cc/ @moT01

Alright, thank you the instructions didn’t specify that. Now, when it comes to the int data type requirement; do I need to have INT data types for 2 columns on each table ? It also doesn’t specify in the instructions.

what does the test say? in total or in each column?

TLDR;

task: each table should have a primary key starting with the name of the table and followed by _id

your solution: creation of composite primary key made of 2 columns planet_id AND name

advice: read instructions twice; do only what you are asked for

I know you may be thinking that the instructions are not clear or the tests are dumb, however, following instructions just as they are, believe it or not, is part of the learning experience.
If the task does not require composite primary key, the test is not really going to expect it either as the outcome will differ.
Moreover, the task asks for primary keys in the format you mentioned yourself

The primary key should be table_name_id as with table planet the primary key is planet_id, not (planet_id, name).

When you \display details for that table, it should give you:

"planet_pkey" PRIMARY KEY, btree (planet_id)

but with your composite primary key, it will result in this:

"planet_pkey" PRIMARY KEY, btree (planet_id, name)

Moreover, when you want to create a reference from moon table to planet table, in order to reference composite primary key(id, name) you also need to create 2 columns in the moon table such as planet_id, planet_name. And again, when you \display details for the moon table, you would get:

Foreign-key constraints:
    "moon_planet_id_planet_name_fkey" FOREIGN KEY (planet_id, planet_name) REFERENCES planet(planet_id, name)

but since you’re asked only for simple primary key called planet_id, I assume the test is looking for a constraint with name "moon_planet_id_fkey" and not "moon_planet_id_planet_name_fkey":

Foreign-key constraints:
    "moon_planet_id_fkey" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)

I think these two things make the test fail.

The task does not require having composite primary key, because it is also not necessary. You declare row’s uniqueness when you set planet_id as a primary key. If you want to make sure that two names are not the same, you can simply add UNIQUE constraint on that column.

One more thing, when the task does not specify something, don’t do it. Do only what is specified.

Hi, I am encountering the same issue. My indexes show Indexes:
“moon_pkey” PRIMARY KEY, btree (moon_id)
but still cannot pass the test.

Please create a new topic for your issue as this issue is already closed by the original poster (and you should only post to someone else’s thread if you are trying to help them)