PostgreSQL foreign-key namespace is unique-per-table, jOOQ forces all foreign keys from all tables into same namespace

[jooq]

PostgreSQL foreign-key namespace is unique-per-table, jOOQ forces all foreign keys from all tables into same namespace

[lukaseder]

That's unfortunate. Thanks for reporting this, I wasn't aware of this. I may have to check other databases as well. This will be solved in 2.0.1 with ticket #986:

https://sourceforge.net/apps/trac/jooq/ticket/986

[mikaelhg]

Excellent, thanks.

[lukaseder]

Interestingly, unique key and primary key names cannot be repeated among tables. I wonder why Postgres allows for repeating foreign key names...

[mikaelhg]

The names PostgreSQL itself autogenerates for anonymous foreign key definitions are of the form tablename_columnname_fkey.

However, the database I was originally running jOOQ against was created by a RAD tool which doesn't follow the same conventions.

CREATE TABLE baz2 (
  id SERIAL PRIMARY KEY,
  c INTEGER
);

CREATE TABLE foo2 (
  id SERIAL PRIMARY KEY,
  baz2_id INTEGER NOT NULL REFERENCES baz2 (id),
  a INTEGER
);

CREATE TABLE bar2 (
  id SERIAL PRIMARY KEY,
  asdf_id INTEGER NOT NULL REFERENCES baz2 (id),
  b INTEGER
);

jooq_fk_testcase=# \d foo2
                          Table "public.foo2"
 Column  |  Type   |                     Modifiers                     
---------+---------+---------------------------------------------------
 id      | integer | not null default nextval('foo2_id_seq'::regclass)
 baz2_id | integer | not null
 a       | integer | 
Indexes:
    "foo2_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "foo2_baz2_id_fkey" FOREIGN KEY (baz2_id) REFERENCES baz2(id)

jooq_fk_testcase=# \d bar2
                          Table "public.bar2"
 Column  |  Type   |                     Modifiers                     
---------+---------+---------------------------------------------------
 id      | integer | not null default nextval('bar2_id_seq'::regclass)
 asdf_id | integer | not null
 b       | integer | 
Indexes:
    "bar2_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "bar2_asdf_id_fkey" FOREIGN KEY (asdf_id) REFERENCES baz2(id)

[lukaseder]

I see. In jOOQ, I have already implemented a similare disambiguation for the Sybase SQL Anywhere database. There, I'm naming key literals [TABLE_NAME]__[KEY_NAME], which works fine with the added test case for #986, also in Postgres. So I'm doing this also for:

• DB2 (allows for reusing PK/UK/FK names)
• Postgres (allows for reusing FK names)
• SQLite (allows for reusing PK/UK/FK names)
• Sybase ASE (allows for reusing PK/UK names)
• Sybase SQL Anywhere (allows for reusing PK/UK/FK names)

These subtle little SQL differences always amaze me...

[mikaelhg]

How about user-defined templates, or template fragments?

I wonder if there is ever going to be a project to systematically document these database peccadilloes, so that the authors of each ORM/database tool don't have to learn each problem through pain, and maintain this knowledge base in their brains?

[lukaseder]

If by user-defined templates, you mean source code generation through Groovy / Velocity / XSLT / ... scripts, this is a pending feature request:
https://sourceforge.net/apps/trac/jooq/ticket/62

It has also been discussed on the user group before:
https://groups.google.com/forum/#!forum/jooq-user

Already today, you can write your own implementation of org.jooq.util.Generator in Java, if you're missing a feature. Besides that, I was proposed a pull request implementing Groovy templating, which should be ready some time in January (although I can't guarantee that this will be done).

On the other hand, if source code generation is left to the user, you won't profit from the ORM / database tool community, which has already gone through many of these issues.

Apart from that, I'm documenting some of those "peccadilloes" in my blog: http://lukaseder.wordpress.com. I doubt that it's even possible to find a "general" solution to abstract all SQL / JDBC, especially if you want to go as far as jOOQ. The jOOQ codebase is full of dialect-specific SQL generation. So you could also consider jOOQ to be such a piece of documentation :-)

[lukaseder]

BTW: This issue is fixed on SVN trunk, GitHub and will be contained in the next Maven SNAPSHOT, later today:
https://oss.sonatype.org/content/repositories/snapshots/org/jooq/