Add runtime model for foreign key ON DELETE and ON UPDATE actions

[lukaseder]

We currently do not yet have any runtime information of ON DELETE and ON UPDATE actions on foreign keys, and can thus not reproduce them in any Meta representation, including:

• [ ] Interpreted Meta
• [ ] InformationSchema
• [ ] DDL Export (see also: #8091)
• [ ] Generated code

[ffays]

Is it still a thing? This issue is open since 3 years now ...

e.g.

import java.util.List;
import org.jooq.ConstraintForeignKeyOnStep;
import org.jooq.ForeignKey;
import org.jooq.Record;
import org.jooq.TableField;
import org.jooq.UniqueKey;
import org.jooq.impl.DSL;
import org.jooq.impl.Internal;
import org.jooq.impl.SQLDataType;
import org.jooq.impl.TableImpl;

public class Reminder extends TableImpl<Record>  {
	@SuppressWarnings("unchecked")
	@Override
	public List<ForeignKey<Record, ?>> getReferences() {
		final ForeignKey<Record, ?> foreignKey = Internal.createForeignKey(this, DSL.name("FK_REMINDER__REMINDERS_ID", new TableField[] { REMINDERS_ID }, REMINDERS.getPrimaryKey(), new TableField[] { REMINDERS.REMINDERS_ID }, true);
		final ConstraintForeignKeyOnStep constraint = (ConstraintForeignKeyOnStep)foreignKey.constraint();
		constraint.onDeleteCascade();  // Issue: this will not lead to the "ON DELETE CASCADE" statement by Data Definition Language generator!
		return List.of(foreignKey);
	}

	@Override
	public UniqueKey<Record> getPrimaryKey() {
		return (UniqueKey<Record>) Internal.createUniqueKey(this, DSL.name("PK_REMINDER"), new TableField[] { REMINDER_ID }, true);
	}

	public Reminder() {
		super(DSL.name("REMINDER"));
	}
	
	private static final long serialVersionUID = 1L;
	public final TableField<Record, Integer> REMINDERS_ID = createField(DSL.name("REMINDERS_ID"), SQLDataType.INTEGER.notNull());
	public final TableField<Record, Integer> REMINDER_ID = createField(DSL.name("REMINDER_ID"), SQLDataType.INTEGER.notNull());
	public final TableField<Record, String> REMINDER_TEXT = createField(DSL.name("REMINDER_TEXT"), SQLDataType.VARCHAR(200).nullable(false));

}

[NikolayMetchev]

For Postgres this information can be retrieved as follows:

select connamespace, conname, c.relname as child_table, p.relname as parent_table, confdeltype from pg_constraint join pg_class c on c.oid=conrelid join pg_class p on p.oid=confrelid

[lukaseder]

Looking into this now for jOOQ 3.20

[lukaseder]

An interesting bit about H2, which doesn't implement the standard SQL default NO ACTION, but only RESTRICT:

• https://github.com/h2database/h2database/issues/4159

This will impact anyone's code generation when using the DDLDatabase in jOOQ, meaning that the generated meta data is slightly more restrictive than the default.

[lukaseder]

Implemented in jOOQ 3.20.0

[jms72]

Hello, despite this issue being closed, in Jooq 3.20 I'm still having the issue that the "ON DELETE CASCADE" suffix is not being produced in Jooq's meta generated DDL string, for e.g. a table with a migration with: ALTER TABLE ONLY some_table ADD CONSTRAINT some_table_some_id_fkey FOREIGN KEY (other_id) REFERENCES other_table(id) ON DELETE CASCADE;

the DDL Jooq generates just has: alter table some_table add constraint "some_table_some_id_fkey" foreign key ("other_id") references other_table ("id");

[lukaseder]

@jms72 Please create a new issue with a complete reproducer. This doesn't reproduce it (using our reproducer template https://github.com/jOOQ/jOOQ-mcve):

diff --git a/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Keys.java b/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Keys.java
index b97c8f2..9d0d2a0 100644
--- a/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Keys.java
+++ b/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Keys.java
@@ -4,11 +4,15 @@
 package org.jooq.mcve.java.postgres;
 
 
+import org.jooq.ForeignKey;
 import org.jooq.TableField;
 import org.jooq.UniqueKey;
 import org.jooq.impl.DSL;
 import org.jooq.impl.Internal;
+import org.jooq.impl.QOM.ForeignKeyRule;
+import org.jooq.mcve.java.postgres.tables.Child;
 import org.jooq.mcve.java.postgres.tables.Test;
+import org.jooq.mcve.java.postgres.tables.records.ChildRecord;
 import org.jooq.mcve.java.postgres.tables.records.TestRecord;
 
 
@@ -23,5 +27,12 @@ public class Keys {
     // UNIQUE and PRIMARY KEY definitions
     // -------------------------------------------------------------------------
 
+    public static final UniqueKey<ChildRecord> PK_CHILD = Internal.createUniqueKey(Child.CHILD, DSL.name("pk_child"), new TableField[] { Child.CHILD.ID }, true);
     public static final UniqueKey<TestRecord> PK_TEST = Internal.createUniqueKey(Test.TEST, DSL.name("pk_test"), new TableField[] { Test.TEST.ID }, true);
+
+    // -------------------------------------------------------------------------
+    // FOREIGN KEY definitions
+    // -------------------------------------------------------------------------
+
+    public static final ForeignKey<ChildRecord, TestRecord> CHILD__SOME_TABLE_SOME_ID_FKEY = Internal.createForeignKey(Child.CHILD, DSL.name("some_table_some_id_fkey"), new TableField[] { Child.CHILD.OTHER_ID }, Keys.PK_TEST, new TableField[] { Test.TEST.ID }, true, ForeignKeyRule.CASCADE, ForeignKeyRule.NO_ACTION);
 }
diff --git a/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Mcve.java b/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Mcve.java
index 72c3756..58f494f 100644
--- a/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Mcve.java
+++ b/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Mcve.java
@@ -11,6 +11,7 @@ import org.jooq.Catalog;
 import org.jooq.Table;
 import org.jooq.impl.DSL;
 import org.jooq.impl.SchemaImpl;
+import org.jooq.mcve.java.postgres.tables.Child;
 import org.jooq.mcve.java.postgres.tables.Test;
 
 
@@ -27,6 +28,11 @@ public class Mcve extends SchemaImpl {
      */
     public static final Mcve MCVE = new Mcve();
 
+    /**
+     * The table <code>mcve.child</code>.
+     */
+    public final Child CHILD = Child.CHILD;
+
     /**
      * The table <code>mcve.test</code>.
      */
@@ -48,6 +54,7 @@ public class Mcve extends SchemaImpl {
     @Override
     public final List<Table<?>> getTables() {
         return Arrays.asList(
+            Child.CHILD,
             Test.TEST
         );
     }
diff --git a/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Tables.java b/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Tables.java
index 8dc630e..27ab3b3 100644
--- a/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Tables.java
+++ b/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/Tables.java
@@ -4,6 +4,7 @@
 package org.jooq.mcve.java.postgres;
 
 
+import org.jooq.mcve.java.postgres.tables.Child;
 import org.jooq.mcve.java.postgres.tables.Test;
 
 
@@ -13,6 +14,11 @@ import org.jooq.mcve.java.postgres.tables.Test;
 @SuppressWarnings({ "all", "unchecked", "rawtypes", "this-escape" })
 public class Tables {
 
+    /**
+     * The table <code>mcve.child</code>.
+     */
+    public static final Child CHILD = Child.CHILD;
+
     /**
      * The table <code>mcve.test</code>.
      */
diff --git a/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/tables/Test.java b/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/tables/Test.java
index 17a452d..238d3c6 100644
--- a/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/tables/Test.java
+++ b/jOOQ-mcve-java-postgres/src/main/java/org/jooq/mcve/java/postgres/tables/Test.java
@@ -8,10 +8,14 @@ import java.util.Collection;
 
 import org.jooq.Condition;
 import org.jooq.Field;
+import org.jooq.ForeignKey;
 import org.jooq.Identity;
+import org.jooq.InverseForeignKey;
 import org.jooq.Name;
+import org.jooq.Path;
 import org.jooq.PlainSQL;
 import org.jooq.QueryPart;
+import org.jooq.Record;
 import org.jooq.SQL;
 import org.jooq.Schema;
 import org.jooq.Select;
@@ -25,6 +29,7 @@ import org.jooq.impl.SQLDataType;
 import org.jooq.impl.TableImpl;
 import org.jooq.mcve.java.postgres.Keys;
 import org.jooq.mcve.java.postgres.Mcve;
+import org.jooq.mcve.java.postgres.tables.Child.ChildPath;
 import org.jooq.mcve.java.postgres.tables.records.TestRecord;
 
 
@@ -88,6 +93,39 @@ public class Test extends TableImpl<TestRecord> {
         this(DSL.name("test"), null);
     }
 
+    public <O extends Record> Test(Table<O> path, ForeignKey<O, TestRecord> childPath, InverseForeignKey<O, TestRecord> parentPath) {
+        super(path, childPath, parentPath, TEST);
+    }
+
+    /**
+     * A subtype implementing {@link Path} for simplified path-based joins.
+     */
+    public static class TestPath extends Test implements Path<TestRecord> {
+
+        private static final long serialVersionUID = 1L;
+        public <O extends Record> TestPath(Table<O> path, ForeignKey<O, TestRecord> childPath, InverseForeignKey<O, TestRecord> parentPath) {
+            super(path, childPath, parentPath);
+        }
+        private TestPath(Name alias, Table<TestRecord> aliased) {
+            super(alias, aliased);
+        }
+
+        @Override
+        public TestPath as(String alias) {
+            return new TestPath(DSL.name(alias), this);
+        }
+
+        @Override
+        public TestPath as(Name alias) {
+            return new TestPath(alias, this);
+        }
+
+        @Override
+        public TestPath as(Table<?> alias) {
+            return new TestPath(alias.getQualifiedName(), this);
+        }
+    }
+
     @Override
     public Schema getSchema() {
         return aliased() ? null : Mcve.MCVE;
@@ -103,6 +141,18 @@ public class Test extends TableImpl<TestRecord> {
         return Keys.PK_TEST;
     }
 
+    private transient ChildPath _child;
+
+    /**
+     * Get the implicit to-many join path to the <code>mcve.child</code> table
+     */
+    public ChildPath child() {
+        if (_child == null)
+            _child = new ChildPath(this, null, Keys.CHILD__SOME_TABLE_SOME_ID_FKEY.getInverseKey());
+
+        return _child;
+    }
+
     @Override
     public Test as(String alias) {
         return new Test(DSL.name(alias), this);
diff --git a/jOOQ-mcve-java-postgres/src/main/resources/db/migration/init.sql b/jOOQ-mcve-java-postgres/src/main/resources/db/migration/init.sql
index 0b6ca27..9d5e584 100644
--- a/jOOQ-mcve-java-postgres/src/main/resources/db/migration/init.sql
+++ b/jOOQ-mcve-java-postgres/src/main/resources/db/migration/init.sql
@@ -7,4 +7,14 @@ CREATE TABLE mcve.test (
   cd INT,
   
   CONSTRAINT pk_test PRIMARY KEY (id) 
-);
\ No newline at end of file
+);
+
+CREATE TABLE mcve.child (
+  id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
+  other_id INT,
+
+  CONSTRAINT pk_child PRIMARY KEY (id)
+);
+
+ALTER TABLE ONLY mcve.child
+    ADD CONSTRAINT some_table_some_id_fkey FOREIGN KEY (other_id) REFERENCES mcve.test (id) ON DELETE CASCADE;
\ No newline at end of file
diff --git a/jOOQ-mcve-java-postgres/src/test/java/org/jooq/mcve/test/java/postgres/JavaTest.java b/jOOQ-mcve-java-postgres/src/test/java/org/jooq/mcve/test/java/postgres/JavaTest.java
index 83a4ee9..9781417 100644
--- a/jOOQ-mcve-java-postgres/src/test/java/org/jooq/mcve/test/java/postgres/JavaTest.java
+++ b/jOOQ-mcve-java-postgres/src/test/java/org/jooq/mcve/test/java/postgres/JavaTest.java
@@ -3,6 +3,7 @@ package org.jooq.mcve.test.java.postgres;
 import org.jooq.DSLContext;
 import org.jooq.SQLDialect;
 import org.jooq.impl.DSL;
+import org.jooq.mcve.java.postgres.DefaultCatalog;
 import org.jooq.mcve.java.postgres.tables.records.TestRecord;
 import org.jooq.tools.JooqLogger;
 import org.junit.After;
@@ -82,14 +83,6 @@ public class JavaTest {
 
     @Test
     public void mcveTest() {
-        assertEquals(1,
-            ctx.insertInto(TEST)
-               .columns(TEST.CD)
-               .values(42)
-               .execute()
-        );
-
-        TestRecord record = ctx.fetchOne(TEST, TEST.CD.eq(42));
-        assertNotNull(record.getId());
+        System.out.println(ctx.meta(DefaultCatalog.DEFAULT_CATALOG).ddl());
     }
 }

I'm getting the expected output for:

System.out.println(ctx.meta(DefaultCatalog.DEFAULT_CATALOG).ddl());

The output being:

create schema "mcve";
create table "mcve"."child" (
  "id" int generated by default as identity not null,
  "other_id" int,
  constraint "pk_child" primary key ("id")
);
create table "mcve"."test" (
  "id" int generated by default as identity not null,
  "cd" int,
  constraint "pk_test" primary key ("id")
);
alter table "mcve"."child" add constraint "some_table_some_id_fkey" foreign key ("other_id") references "mcve"."test" ("id") on delete cascade on update no action;

[jms72]

Appreciate the response. Digging deeper, it looks like we were still using jooq 3.19 due to compatibility with org.springframework.boot's included version. So I assume it will work ok with jooq >= 3.20.