BUG: Many-to-many references not honouring schema syntax

Report Bugs
1

I’m using dbdiagram.io to generate a multi-schema database (in PostgreSQL). However, when I create a many-to-many reference using the <> syntax, the resulting link table is always created in the public schema.

I have tried adding a name to the reference (e.g. Ref mySchema.myTable: source.id <> dest.id), which throws a syntax error (at the “.” between mySchema and myTable).

I can work around this for now by manually creating the link tables; but long term, this would be really good to fix.

2

This is the correct syntax for a many-to-many relationship belongs to a schema:
Ref name_optional: schema1.table1.column1 <> schema1.table2.column2

For more information, you can check out our DBML docs.

Thank you.

3

Hi,

According to the document:

There are 4 types of relationships: one-to-one, one-to-many, many-to-one and many-to-many

  • < : one-to-many. E.g: users.id < posts.user_id
  • > : many-to-one. E.g: posts.user_id > users.id
  • - : one-to-one. E.g: users.id - user_infos.user_id
  • <> : many-to-many. E.g: authors.id <> books.id

I need the last one, not the first one (which is the syntax you suggest).

For example:

Table schema.image {
    id integer [pk]
    url varchar
}

Table schema.content_item {
    id integer [pk]
    heading varchar
    description varchar
}

Ref: schema.image.id <> schema.content_item.id

Table schema.footer_item {
    id integer [pk]
    left varchar
    centre varchar
    right varchar
}

Ref: schema.image.id <> schema.footer_item.id

…results in:

CREATE SCHEMA "schema";

CREATE TABLE "schema"."image" (
  "id" integer PRIMARY KEY,
  "url" varchar
);

CREATE TABLE "schema"."content_item" (
  "id" integer PRIMARY KEY,
  "heading" varchar,
  "description" varchar
);

CREATE TABLE "schema"."footer_item" (
  "id" integer PRIMARY KEY,
  "left" varchar,
  "centre" varchar,
  "right" varchar
);

CREATE TABLE "image_content_item" (
  "image_id" integer NOT NULL,
  "content_item_id" integer NOT NULL,
  PRIMARY KEY ("image_id", "content_item_id")
);

ALTER TABLE "image_content_item" ADD FOREIGN KEY ("image_id") REFERENCES "schema"."image" ("id");

ALTER TABLE "image_content_item" ADD FOREIGN KEY ("content_item_id") REFERENCES "schema"."content_item" ("id");


CREATE TABLE "image_footer_item" (
  "image_id" integer NOT NULL,
  "footer_item_id" integer NOT NULL,
  PRIMARY KEY ("image_id", "footer_item_id")
);

ALTER TABLE "image_footer_item" ADD FOREIGN KEY ("image_id") REFERENCES "schema"."image" ("id");

ALTER TABLE "image_footer_item" ADD FOREIGN KEY ("footer_item_id") REFERENCES "schema"."footer_item" ("id");

What I want it to do:

CREATE SCHEMA "schema";

CREATE TABLE "schema"."image" (
  "id" integer PRIMARY KEY,
  "url" varchar
);

CREATE TABLE "schema"."content_item" (
  "id" integer PRIMARY KEY,
  "heading" varchar,
  "description" varchar
);

CREATE TABLE "schema"."footer_item" (
  "id" integer PRIMARY KEY,
  "left" varchar,
  "centre" varchar,
  "right" varchar
);

CREATE TABLE "schema"."image_content_item" (
  "image_id" integer NOT NULL,
  "content_item_id" integer NOT NULL,
  PRIMARY KEY ("image_id", "content_item_id")
);

ALTER TABLE "schema"."image_content_item" ADD FOREIGN KEY ("image_id") REFERENCES "schema"."image" ("id");

ALTER TABLE "schema"."image_content_item" ADD FOREIGN KEY ("content_item_id") REFERENCES "schema"."content_item" ("id");


CREATE TABLE "schema"."image_footer_item" (
  "image_id" integer NOT NULL,
  "footer_item_id" integer NOT NULL,
  PRIMARY KEY ("image_id", "footer_item_id")
);

ALTER TABLE "schema"."image_footer_item" ADD FOREIGN KEY ("image_id") REFERENCES "schema"."image" ("id");

ALTER TABLE "schema"."image_footer_item" ADD FOREIGN KEY ("footer_item_id") REFERENCES "schema"."footer_item" ("id");

(note “schema” in front of “image_content_item” & “image_footer_item” tables…

1 Like
4

We can see the problem now. We’ve noted the issue & will fix it soon.

Proposed solution:

  • When generating SQL, a junction table in an n-to-n relationship will belong to the schema holding the left table defined in the syntax.
  • Examples:

    • Ref schema.table1.column1 <> schema.table2.column2 → will create a junction table: "schema"."table1_table2"

    • Ref schema1.table1.column1 <> schema2.table2.column2 → will create a junction table "schema1"."table1_table2"

Thank you for reporting the issue.

5

No worries! Proposed solution looks good (but maybe, as an enhancement, allow the ref name to take a schema name as well? e.g.
Ref schema2.junction_name: schema1.table1.column1 <> schema2.table2.column2 (if I, for some reason, wanted the junction table in schema2).

That’d just be a nice-to-have in my book though :slight_smile:

1 Like
6

Since we don’t support config the relationship schema yet, this is our short-term solution. That said, we will consider allowing users to define attributes of junction tables in n-to-n relationships.

With the current proposed solution, if you want the junction table in schema2, maybe you can put the schema2.table2.column2 to the left as a workaround. Does it make sense to you?

1 Like
7

Sure, that’d work - it was a hypothetical to be honest, all of my many-to-many linked tables are in the same schema.

Sidebar: I would (one day!) like to be able to link multiple dbml files together & have dbdiagram.io work out the relationships across schemas (e.g. project1.dbml contains “schema1”, project2.dbml contails “schema2”; but there’s a relationship between “schema1”.“master1” and “schema2”.“master2”) - but that’s a whole 'nother kettle of fish…

1 Like
8

Hi AdeV,
As proposed, the SQL code generation for many-to-many relations has now been updated. You can try it out. Please let us know if you require any additional assistance.
Thank you very much!

1 Like
9

Cool, thank you! I’ll give that a try :slight_smile: :heart_eyes:

1 Like