Postgres Foreign-key constraints in non public schema

Question

I have a question regarding constraints on custom schemas. My app creates a new/separate schema for each clients corresponding with clients' name (i.e .clienta, clientb,...). Some of the tables have a foreign-key constraints but, they don't work on schemas other than the default public schema. For example, let's say there is schema called clienta and it has projects and tasks tables, model Task has a belongsTo(models.Project) association (i.e projects table primary_key is a foreign_key for table tasks. The issue starts here: when trying to create a record in table tasks there comes an error saying foreign key violation error... Key (project_id)=(1) is not present in table "projects... even though projects table has the respective record with id = 1. I am wording if this is a limitation of sequelize library itself or am I missing something in the configs?

Sequelize config

"development": {
    "database": "my_app",
    "host": "127.0.0.1",
    "dialect": "postgres",
    "operatorsAliases": "Sequelize.Op",
    "dialectOptions": {
      "prependSearchPath": true
    },
    "define": {
      "underscored": true
    }
  }

Example of create function:

models.Task.create({...args}, { searchPath: 'clienta' })

N.B Everything works as expected in public schema.

Please post the exact DDL that fails, and the error you while tryng to execute it. — The Impaler, Jul 14 '18 at 12:40
The main message in the error log the one I posted above. The question is not specific to one issue. It is more of a config/setup or knowing what sequelize library can offer and cant'. — bir_ham, Aug 03 '18 at 15:49
@bir_ham The problem seems to be that the tables that are created have constraints that are not correctly lined up if they have a schema that is different from public. The minimum information required would be how you have defined the constraints there. If I were a betting man, I would guess that your constraints for 'clienta' tables actually depend on tables with schema 'public' - but that constitutes gambling (this can be checked rather easily using pgAdmin or any tool like that). — Koen, Aug 08 '18 at 06:08
@Koen That was also my initial assumption and removed the constraints for tables in public schema. However, constraints of tables within the same schemas keeps on falling. As I stated in my question table *projects* and *tasks* lives in same schema (*clienta*, *clientb*, or any schema) — bir_ham, Aug 10 '18 at 16:45
@bir_ham - the question is - when you define the constraint - do you refer to the public table or to the specific table within the same schema? Hence the request for the definition. — Koen, Aug 10 '18 at 17:14
@Koen You're very right! My constraints were prefixed with 'public'. I created my tables dynamically using sequelize's model `sync` helper (e.g Project.sync({schema: testa})) and this indeed creates a table however, not enough to assign constraints to corresponding schema. I added the `searchPath` options to `sync` (e.g `Model.sync({schema: testa, searchPath: testa})`) and now got it work. Thanks! You deserve the bounty prize! — bir_ham, Aug 11 '18 at 00:27

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

The sync method API lists two options relating to DB-schema:

options.schema - schema the table should be created in

options.searchPath - optional parameter to set searchPath (Postgresql)

When using schemas other than the default and an association between Models has been created (using for instance belongsTo), it is important to set the searchPath to hold the name of the schema of the target table. Following the explanation in search_path in postgresql, not specifying the searchPath will have the constraint referring to a table (if it exists) in the default schema (usually 'public').

Postgres Foreign-key constraints in non public schema

1 Answers1