I am having troubles with my foreign keys …an error keeps saying constraint does not exist
what is the exact error?
what is your schema?
i need to see your schema.
Type out the commands \l star and \l galaxy for example and show me the results
(the error says that you need to define a foreign key in the star table so I need to see what you have defined there)
universe=> \d planet
Table "public.planet"
+-----------+--------------+-----------+----------+-------------------------------------------+
| Column | Type | Collation | Nullable | Default |
+-----------+--------------+-----------+----------+-------------------------------------------+
| planet_id | integer | | not null | nextval('planet_planet_id_seq'::regclass) |
| name | text | | | |
| size | numeric(9,0) | | | |
+-----------+--------------+-----------+----------+-------------------------------------------+
Indexes:
"planet_pkey" PRIMARY KEY, btree (planet_id)
"fk_planet" UNIQUE CONSTRAINT, btree (planet_id)
universe=> ALTER TABLE planet ADD FOREIGN KEY (star_id) REFERENCES star(star_id);
ERROR: column "star_id" referenced in foreign key constraint does not exist
universe=>
you have only shown the planet table
what about the star table?
what does \l star return?
universe=> ALTER TABLE star ADD FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id);
ERROR: column "galaxy_id" referenced in foreign key constraint does not exist
universe=> \d star;
Table "public.star"
+-------------+---------+-----------+----------+---------------------------------------+
| Column | Type | Collation | Nullable | Default |
+-------------+---------+-----------+----------+---------------------------------------+
| star_id | integer | | not null | nextval('star_star_id_seq'::regclass) |
| name | text | | | |
| planet_type | boolean | | not null | |
+-------------+---------+-----------+----------+---------------------------------------+
Indexes:
"star_pkey" PRIMARY KEY, btree (star_id)
"fk_star" UNIQUE CONSTRAINT, btree (star_id)
Still giving the same error
You keep sending me errors without giving me all the information.
If you are trying to add a foreign key I need to see all the tables schema that you are referencing. (For eg now you are trying to add a foreign key from galaxy table)
Please just get the schema for all your tables instead of sending it one by one.
universe=> \d galaxy
Table "public.galaxy"
+-----------+---------+-----------+----------+-------------------------------------------+
| Column | Type | Collation | Nullable | Default |
+-----------+---------+-----------+----------+-------------------------------------------+
| galaxy_id | integer | | not null | nextval('galaxy_galaxy_id_seq'::regclass) |
| name | text | | | |
| position | boolean | | not null | |
+-----------+---------+-----------+----------+-------------------------------------------+
Indexes:
"galaxy_pkey" PRIMARY KEY, btree (galaxy_id)
"fk_galaxy" UNIQUE CONSTRAINT, btree (galaxy_id)
universe=> \d star
Table "public.star"
+-------------+---------+-----------+----------+---------------------------------------+
| Column | Type | Collation | Nullable | Default |
+-------------+---------+-----------+----------+---------------------------------------+
| star_id | integer | | not null | nextval('star_star_id_seq'::regclass) |
| name | text | | | |
| planet_type | boolean | | not null | |
+-------------+---------+-----------+----------+---------------------------------------+
Indexes:
"star_pkey" PRIMARY KEY, btree (star_id)
"fk_star" UNIQUE CONSTRAINT, btree (star_id)
universe=> \d planet
Table "public.planet"
+-----------+--------------+-----------+----------+-------------------------------------------+
| Column | Type | Collation | Nullable | Default |
+-----------+--------------+-----------+----------+-------------------------------------------+
| planet_id | integer | | not null | nextval('planet_planet_id_seq'::regclass) |
| name | text | | | |
| size | numeric(9,0) | | | |
+-----------+--------------+-----------+----------+-------------------------------------------+
Indexes:
"planet_pkey" PRIMARY KEY, btree (planet_id)
"fk_planet" UNIQUE CONSTRAINT, btree (planet_id)
universe=> \d moon
Table "public.moon"
+---------------------+--------------+-----------+----------+---------------------------------------+
| Column | Type | Collation | Nullable | Default |
+---------------------+--------------+-----------+----------+---------------------------------------+
| moon_id | integer | | not null | nextval('moon_moon_id_seq'::regclass) |
| name | text | | | |
| distance_from_earth | numeric(9,0) | | | |
| size | numeric(9,0) | | | |
+---------------------+--------------+-----------+----------+---------------------------------------+
Indexes:
"moon_pkey" PRIMARY KEY, btree (moon_id)
"fk_moon" UNIQUE CONSTRAINT, btree (moon_id)
This is what you asked for right
I noticed all your tables have an unexpected default value setting.
Is that something the exercise asked for?
(If yes, can I see the instructions for this default setting?)
One other concern.
This message is saying that you don’t have a galaxy_id column to altar in your star table (and yes your star table is missing that column).
So you can try adding galaxy_id column first to star as a foreign key
no i wasn’t to give any
universe=> SELECT * FROM galaxy;
±----------±-------------------±---------+
| galaxy_id | name | position |
±----------±-------------------±---------+
| 1 | Milk Way Galaxy | t |
| 2 | Elliptical Galaxy | f |
| 3 | Messier 81 Galaxy | t |
| 4 | Black Eye Galaxy | f |
| 5 | Hoag Object Galaxy | t |
| 6 | Whirlpool Galaxy | f |
| 7 | Pinwheel Galaxy | t |
±----------±-------------------±---------+
(7 rows)
universe=> ALTER TABLE galaxy ADD FOREIGN KEY (star_id) REFERENCES star(star_id);
ERROR: column “star_id” referenced in foreign key constraint does not exist
universe=> SELECT * FROM star;
±--------±-----±------------+
| star_id | name | planet_type |
±--------±-----±------------+
±--------±-----±------------+
(0 rows)
it still didn’t work
So what I am saying to you is, you cannot make a column a foreign key unless you create that column first.
So for eg in the star table you must first add a column called galaxy_id and make that new column the foreign key
Thanks a lot, i have gotten it now
1 Like