I am running jooq 3.10.1
Here is a code snippet with your SELECT statement and its output. As you will see, the ALTER from the DDL print out do not have the names of the constraints. But, as you have noted, with your select statement, clearly the names of the constraints are in the DB system tables. We agree that the generated java code has the constraint names.
However, the SQL generated from the DSLContext ddl() method is not capturing the constraint names.******* code *****
List<Schema> schemas = create.meta().getSchemas();
Queries ddl = null;
for (Schema s : schemas) {
if (s.getName().equals("APP")) {
ddl = create.ddl(s);
break;
}
}
for (Query query : ddl.queries()) {
System.out.println(query);
}
String sql = "SELECT fc.constraintname,ft.tablename,fs.schemaname,fg.descriptor,pc.constraintname,ps.schemaname FROM sys.sysconstraints fc JOIN sys.sysforeignkeys f ON f.constraintid = fc.constraintid JOIN sys.sysconglomerates fg ON fg.conglomerateid = f.conglomerateid JOIN sys.systables ft ON ft.tableid = fg.tableid JOIN sys.sysschemas fs ON ft.schemaid = fs.schemaid JOIN sys.sysconstraints pc ON pc.constraintid = f.keyconstraintid JOIN sys.sysschemas ps ON pc.schemaid = ps.schemaid WHERE cast(fc.type as varchar(32672)) = 'F'";
System.out.println(create.resultQuery(sql).fetch().format());
****** end code ****
------------ Output from the code -----------
create schema "APP"
create table "APP"."ARC"(
"ID" integer not null,
"EDGE" integer,
"PRODUCT" integer,
"PRIORITY" integer,
constraint "SQL171130162547050"
create table "APP"."DEMAND"(
"ID" integer not null,
"TERMINAL" integer,
"PRODUCT" integer,
"PERIOD" integer,
"AMOUNT" integer,
constraint "SQL171130162546990"
create table "APP"."EDGE"(
"ID" integer not null,
"ORIGIN" integer,
"DESTINATION" integer,
"VEHICLE" integer,
"TRANSITTIME" double,
constraint "SQL171130162547031"
create table "APP"."ERQ"(
"ID" integer not null,
"TERMINAL" integer,
"PRODUCT" integer,
"MODE" integer,
"AMOUNT" integer,
constraint "SQL171130162547010"
create table "APP"."INGREDIENT"(
"ID" integer not null,
"RECIPE" integer,
"PRODUCT" integer,
"AMOUNT" integer,
constraint "SQL171130162546980"
create table "APP"."MODE"(
"ID" integer not null,
"CODE" varchar(1),
"NAME" varchar(11),
constraint "SQL171130162546870"
create table "APP"."OPERATION"(
"ID" integer not null,
"CODE" varchar(1),
"NAME" varchar(10),
constraint "SQL171130162546920"
create table "APP"."PIPELINE"(
"ROUTE" integer not null,
"CAPACITY" integer,
"MAXBATCH" integer,
"MINBATCH" integer,
"MAXRATE" integer,
"MINRATE" integer,
"MININTERVAL" integer,
constraint "SQL171130162546960"
primary key ("ROUTE")
)
create table "APP"."PRODUCT"(
"ID" integer not null,
"NAME" varchar(4),
"ADDITIVE" boolean not null,
"DESCRIPTION" varchar(255),
constraint "SQL171130162546930"
create table "APP"."RECIPE"(
"ID" integer not null,
"PRODUCT" integer,
"DESCRIPTION" varchar(255),
constraint "SQL171130162546971"
create table "APP"."RESOURCE"(
"ID" integer not null,
"TERMINAL" integer,
"MODE" integer,
"NAME" varchar(30),
"THROUGHPUT" integer,
"SPACES" integer,
"VEHICLETIME" double,
"PUMPRATE" integer,
constraint "SQL171130162547000"
create table "APP"."RESOURCEOPERATION"(
"ID" integer not null,
"TERMINALOPERATION1" integer,
"TERMINALOPERATION2" integer,
constraint "SQL171130162547030"
create table "APP"."ROUTE"(
"ID" integer not null,
"NAME" varchar(30),
"CIRCULAR" boolean not null,
constraint "SQL171130162546950"
create table "APP"."ROUTEARC"(
"ID" integer not null,
"ROUTE" integer,
"ARC" integer,
"STOP" integer,
"LOADING" varchar(255),
constraint "SQL171130162547060"
create table "APP"."SCHEDULEDRECEIPT"(
"ID" integer not null,
"ARC" integer,
"PERIOD" integer,
"AMOUNT" integer,
constraint "SQL171130162547070"
create table "APP"."SKU"(
"ID" integer not null,
"TERMINAL" integer,
"PRODUCT" integer,
"INVENTORY" integer,
"MAXAUTHORIZED" integer,
"MAXFILL" integer,
"CONTROLLIMIT" integer,
constraint "SQL171130162547001"
create table "APP"."TERMINAL"(
"ID" integer not null,
"NAME" varchar(30),
"LOCATION" varchar(30),
"TYPE" varchar(10),
"OWNER" varchar(4),
"MAXHOURS" integer,
"INJECTION" boolean not null,
constraint "SQL171130162546940"
create table "APP"."TERMINALOPERATION"(
"ID" integer not null,
"RESOURCE" integer,
"OPERATION" integer,
"PRODUCT" integer,
"SPACES" integer,
constraint "SQL171130162547020"
create table "APP"."VEHICLETYPE"(
"ID" integer not null,
"NAME" varchar(20),
"MODE" integer,
"SIZE" integer,
"FLEET" integer,
"TANKS" integer,
constraint "SQL171130162546970"
primary key ("ID")
)
alter table "APP"."ARC"
add constraint
foreign key ("EDGE")
references "EDGE" ("ID")
alter table "APP"."ARC"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."DEMAND"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."DEMAND"
add constraint
foreign key ("TERMINAL")
references "TERMINAL" ("ID")
alter table "APP"."EDGE"
add constraint
foreign key ("DESTINATION")
references "RESOURCE" ("ID")
alter table "APP"."EDGE"
add constraint
foreign key ("ORIGIN")
references "RESOURCE" ("ID")
alter table "APP"."EDGE"
add constraint
foreign key ("VEHICLE")
references "VEHICLETYPE" ("ID")
alter table "APP"."ERQ"
add constraint
foreign key ("MODE")
references "MODE" ("ID")
alter table "APP"."ERQ"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."ERQ"
add constraint
foreign key ("TERMINAL")
references "TERMINAL" ("ID")
alter table "APP"."INGREDIENT"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."INGREDIENT"
add constraint
foreign key ("RECIPE")
references "RECIPE" ("ID")
alter table "APP"."PIPELINE"
add constraint
foreign key ("ROUTE")
references "ROUTE" ("ID")
alter table "APP"."RECIPE"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."RESOURCE"
add constraint
foreign key ("MODE")
references "MODE" ("ID")
alter table "APP"."RESOURCE"
add constraint
foreign key ("TERMINAL")
references "TERMINAL" ("ID")
alter table "APP"."RESOURCEOPERATION"
add constraint
foreign key ("TERMINALOPERATION2")
references "TERMINALOPERATION" ("ID")
alter table "APP"."RESOURCEOPERATION"
add constraint
foreign key ("TERMINALOPERATION1")
references "TERMINALOPERATION" ("ID")
alter table "APP"."ROUTEARC"
add constraint
foreign key ("ARC")
references "ARC" ("ID")
alter table "APP"."ROUTEARC"
add constraint
foreign key ("ROUTE")
references "ROUTE" ("ID")
alter table "APP"."SCHEDULEDRECEIPT"
add constraint
foreign key ("ARC")
references "ARC" ("ID")
alter table "APP"."SKU"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."SKU"
add constraint
foreign key ("TERMINAL")
references "TERMINAL" ("ID")
alter table "APP"."TERMINALOPERATION"
add constraint
foreign key ("OPERATION")
references "OPERATION" ("ID")
alter table "APP"."TERMINALOPERATION"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."TERMINALOPERATION"
add constraint
foreign key ("RESOURCE")
references "RESOURCE" ("ID")
alter table "APP"."VEHICLETYPE"
add constraint
foreign key ("MODE")
references "MODE" ("ID")
+-----------------------------------+-----------------+----------+----------+------------------+----------+
|CONSTRAINTNAME |TABLENAME |SCHEMANAME|DESCRIPTOR|CONSTRAINTNAME |SCHEMANAME|
+-----------------------------------+-----------------+----------+----------+------------------+----------+
|MODEVEHICLETYPE |VEHICLETYPE |APP |BTREE (3) |SQL171130162546870|APP |
|MODERESOURCE |RESOURCE |APP |BTREE (3) |SQL171130162546870|APP |
|MODEERQ |ERQ |APP |BTREE (4) |SQL171130162546870|APP |
|OPERATIONTERMINALOPERATION |TERMINALOPERATION|APP |BTREE (3) |SQL171130162546920|APP |
|PRODUCTRECIPE |RECIPE |APP |BTREE (2) |SQL171130162546930|APP |
|PRODUCTINGREDIENT |INGREDIENT |APP |BTREE (3) |SQL171130162546930|APP |
|PRODUCTDEMAND |DEMAND |APP |BTREE (3) |SQL171130162546930|APP |
|PRODUCTSKU |SKU |APP |BTREE (3) |SQL171130162546930|APP |
|PRODUCTERQ |ERQ |APP |BTREE (3) |SQL171130162546930|APP |
|PRODUCTARC |ARC |APP |BTREE (3) |SQL171130162546930|APP |
|PRODUCTTERMINALOPERATION |TERMINALOPERATION|APP |BTREE (4) |SQL171130162546930|APP |
|TERMINALDEMAND |DEMAND |APP |BTREE (2) |SQL171130162546940|APP |
|TERMINALSKU |SKU |APP |BTREE (2) |SQL171130162546940|APP |
|TERMINALRESOURCE |RESOURCE |APP |BTREE (2) |SQL171130162546940|APP |
|TERMINALERQ |ERQ |APP |BTREE (2) |SQL171130162546940|APP |
|PIPELINEROUTE |PIPELINE |APP |BTREE (1) |SQL171130162546950|APP |
|ROUTEROUTEARC |ROUTEARC |APP |BTREE (2) |SQL171130162546950|APP |
|VEHICLETYPEEDGE |EDGE |APP |BTREE (4) |SQL171130162546970|APP |
|RECIPEINGREDIENT |INGREDIENT |APP |BTREE (2) |SQL171130162546971|APP |
|ORIGINEDGE |EDGE |APP |BTREE (2) |SQL171130162547000|APP |
|DESTINATIONEDGE |EDGE |APP |BTREE (3) |SQL171130162547000|APP |
|RESOURCETERMINALOPERATION |TERMINALOPERATION|APP |BTREE (2) |SQL171130162547000|APP |
|TERMINALOPERATIONRESOURCEOPERATION |RESOURCEOPERATION|APP |BTREE (2) |SQL171130162547020|APP |
|TERMINALOPERATIONRESOURCEOPERATION1|RESOURCEOPERATION|APP |BTREE (3) |SQL171130162547020|APP |
|EDGEARC |ARC |APP |BTREE (2) |SQL171130162547031|APP |
|ARCSHIPMENT |SCHEDULEDRECEIPT |APP |BTREE (2) |SQL171130162547050|APP |
|ARCROUTEARC |ROUTEARC |APP |BTREE (3) |SQL171130162547050|APP |
+-----------------------------------+-----------------+----------+----------+------------------+----------+