Creating a data model from scratch: Adding a Foreign Key in PostgreSQL

Question

as personal project, I'm creating a small data model from a dataset.

Right now I'm facing issues in creating a foreign key between two tables.

CREATE TABLE IF NOT EXISTS public."Years"
(
    "ID_Year" integer,
    "Year" integer,
    PRIMARY KEY ("ID_Year")
);

CREATE TABLE IF NOT EXISTS public."SendingCountries"
(
    "ID_Send_Country" integer,
    "Year2Years" integer,
    "Country2Countries" integer,
    "Female_legis2FemaleLegislators" integer,
    "FeministPolicy" boolean,
    PRIMARY KEY ("ID_Send_Country")
);

I've used this code:

ALTER TABLE SendingCountries
ADD CONSTRAINT FK_SendingCountries_Years
FOREIGN KEY (Year2Years)
REFERENCES Years(ID_Year);

but I have this output: "ERROR: relation "sendingcountries" does not exist"

Can anyone highlight what I'm doing wrong?

thanks

My goal is to have the column "Year2Years" , from table SendingCountries, as Foreign key of ID_Year of Table Years

Don't use UPPER case, just don't. You're shooting yourself in the foot, you will always have bugs and typos in your code. Forget the existence of UPPER case and double quotes " in your SQL. Or prepare yourself for headaches and frustration. — Frank Heikens
– Frank Heikens, Commented May 2, 2024 at 20:49

Mohiuddin Sumon · Accepted Answer · 2024-05-02 19:10:18Z

1

I can think of two reason from your code:

Case sensitivity: PostgreSQL by default converts unquoted identifiers to lowercase. Ensure that the table name matches the casing used in your SQL query.
Schema qualification: Ensure that the table belongs to the correct schema.

Based on the provided code, it seems like you're using double quotes around table and column names, which means PostgreSQL will retain the casing you provide. Double-check the casing in your SQL query.

can you try:

ALTER TABLE public."SendingCountries"
ADD CONSTRAINT FK_SendingCountries_Years
FOREIGN KEY ("Year2Years")
REFERENCES public."Years"("ID_Year");

answered May 2, 2024 at 19:10

Mohiuddin Sumon

1522 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fab Over a year ago

Thanks! It worked! It was a case sensitivity issue

Collectives™ on Stack Overflow

Creating a data model from scratch: Adding a Foreign Key in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related