Question 387940 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Foreign key is not correct after flywaydb migration in postgress and springboot

I have 4 tenants – tenant1,tenant2,tenant3,tenant4,tenant5
and i am using spring boot, postgress database and flywaydb Migration to database version and auto create when new tenant registered.
Base code : https://github.com/AnarSultanov/examples/tree/master/schema-based-multi-tenancy

https://sultanov.dev/blog/schema-based-multi-tenancy-with-spring-data/

in my sql scripts i have added foreign key but after flyway db initializes and app runs
all foreign key for each schema/table are referring to all schema

one schema can access another foreign key table reference

enter image description here

enter image description here

Foreign keys are creating number of tenant present

enter image description here
this ll generate the schema by looping from the user/tenant table
after fly migrate run successfully , foreign keys are creating multiple times (same count as schema present).

Correct behavior should create only one foriegn key per schema.

sample code to reproduce the issue
https://github.com/jeebendu/schema-based-multi-tenancy
clone and connect to any postgress db , it ll automatic create the 5 tenants

Correct behavior should be only one foreign key
enter image description here

Tenant_1 added Author_t1
While inserting record in Tenant_3 its refering to tenant1 foriegn key data

enter image description here
enter image description here

Can any one help to this ?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Its not an issue in Flywaydb or Postgress Its a issue on heidiSQL Client

    Raised request @ https://github.com/HeidiSQL/HeidiSQL/issues/1956


  2. 0

    You could try making all references to tables have the schema in the reference. Flyway allows you to specify a placeholder to help with multi-tenant schemas: https://www.red-gate.com/blog/handling-multiple-schemas-in-the-same-database-with-flyway

    CREATE TABLE ${flyway:defaultSchema}.MyTable (id INT);

    Other than that – it isn’t clear what the exact issue is – could you recreate it in a small reproduction that you can share?

    I’ve downloaded the code, and added in the FK from author to users.

    I believe there is an issue in the SQL Query used to examine the FK’s – the join to ccu didn’t include a condition that the schema’s matched and therefore there were extra rows.

    This query seems to show that there are the right number of FK’s – one per tenant back to the users table:

    select tc.table_schema,
       tc.constraint_type,
       tc.constraint_name,
       tc.table_name,
       kcu.column_name,
       ccu.table_schema AS foreign_table_schema,
       ccu.table_name AS foreign_table_name,
       ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints as tc
    JOIN information_schema.key_column_usage as kcu
    on tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage as ccu
   ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
--AND tc.table_schema = 'tenant1'
and tc.table_name = 'note';

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search