CODEWITHSUNDEEP
postgresqlforeign-keysdatabase-schemaddl
Roby Sottini
Roby Sottini

Reputation: 2265

How to add foreign key using a non public schema in PostgreSQL?

I am trying to add a foreign key with this psql command:

places_local=> ALTER TABLE prestamos_bienes.bienes
ADD CONSTRAINT "bienes_prestamos_bienes.marcas_id_marca_fk"
FOREIGN KEY (id_marca) REFERENCES "prestamos_bienes.marcas" (id_marca);

But I get this error:

ERROR: no existe la relación «prestamos_bienes.marcas»

It means:

ERROR: relation «prestamos_bienes.marcas» does not exist

The PostgreSQL error codes is 42P01 (undefined_table).

My database is: places_local

My schema is: prestamos_bienes

My tables are: bienes and marcas

Upvotes: 0

Views: 1872

Answers (1)

user330315
user330315

Reputation:

The identifier "prestamos_bienes.marcas" is name without a schema qualification.

Each part of a fully qualified name needs to be quoted individually. 

ALTER TABLE prestamos_bienes.bienes
   ADD CONSTRAINT "bienes_prestamos_bienes.marcas_id_marca_fk"
   FOREIGN KEY (id_marca) REFERENCES "prestamos_bienes"."marcas" (id_marca);

Or better without quotes completely: 

ALTER TABLE prestamos_bienes.bienes
   ADD CONSTRAINT bienes_marcas_fk
   FOREIGN KEY (id_marca) REFERENCES prestamos_bienes.marcas (id_marca);

In general, you should avoid the use of double quotes completely.

Then it might also be clear what you intended with the foreign key name: "bienes_prestamos_bienes.marcas_id_marca_fk". That also is not a schema qualified name.

Upvotes: 1

Related Questions