Reverese Engineer Exasol Primary Key Not Null Constraints

[ssteinhauser]

When reverse engineering an existing Exasol table, I've experienced different unexpected behavior with regards to primary keys and not null constraints.

Let's have a look at two cases, where in both cases, the primary key is reverse engineered as unique key, although unique keys do not exist in Exasol. This is most likely due to the behavior of the not null constraints I will describe in the following:

1. First of all let's have a look at the case, when the primary key is enabled:

   create or replace table test.test (
       id1 integer primary key enable
   );

   As already mentioned, the primary key will be reverse engineered as unique key. There is actually no explicit not null constraint defined in the catalog (sys.exa_all_constraints):

   select *
   from sys.exa_all_constraints
   where constraint_schema = 'TEST'
     and constraint_table = 'TEST'
     and constraint_type = 'NOT NULL'
   ;
   
   --> This results in an empty result set

   But when quering the column informations, we can see, that the column is not nullable:

   select column_schema, column_table, column_name, column_is_nullable
   from sys.exa_all_columns
   where column_schema = 'TEST'
     and column_table = 'TEST'
     and column_name = 'ID1'
   ;
   
   --> results in the following
   +-------------+------------+-----------+------------------+
   |COLUMN_SCHEMA|COLUMN_TABLE|COLUMN_NAME|COLUMN_IS_NULLABLE|
   +-------------+------------+-----------+------------------+
   |TEST         |TEST        |ID1        |false             |
   +-------------+------------+-----------+------------------+

   The following is the output of the reverse engineering logs:

   CREATE OR REPLACE TABLE "TEST"."TEST"(
       "ID1" DECIMAL(18,0)
   )
   
   Parsed.
   
   ALTER TABLE "TEST"."TEST" add constraint "SYS_13271067632772203642000591080448" PRIMARY KEY("ID1") ENABLE
   
   Parsed.
   
   COMMENT ON TABLE "TEST"."TEST" is ''
   
   Parsed.

2. Now, let's have a look at the second case, where the primary key is disabled:

   create or replace table test.test (
       id1 integer primary key disable
   );

   Also here, the primary key will be reverse engineered as unique key. There is actually no explicit not null constraint defined in the catalog (sys.exa_all_constraints):

   select *
   from sys.exa_all_constraints
   where constraint_schema = 'TEST'
     and constraint_table = 'TEST'
     and constraint_type = 'NOT NULL'
   ;
   
   --> This results in an empty result set

   But when quering the column informations, we can see, that the column is nullable and due to the disable primary key, null values are actually allowed:

   select column_schema, column_table, column_name, column_is_nullable
   from sys.exa_all_columns
   where column_schema = 'TEST'
     and column_table = 'TEST'
     and column_name = 'ID1'
   ;
   
   --> results in the following
   +-------------+------------+-----------+------------------+
   |COLUMN_SCHEMA|COLUMN_TABLE|COLUMN_NAME|COLUMN_IS_NULLABLE|
   +-------------+------------+-----------+------------------+
   |TEST         |TEST        |ID1        |true              |
   +-------------+------------+-----------+------------------+

   The following is the output of the reverse engineering logs:

   CREATE OR REPLACE TABLE "TEST"."TEST"(
       "ID1" DECIMAL(18,0)
   )
   
   Parsed.
   
   ALTER TABLE "TEST"."TEST" add constraint "SYS_13271067633506650420000591122432" PRIMARY KEY("ID1") DISABLE
   
   Parsed.
   
   COMMENT ON TABLE "TEST"."TEST" is ''
   
   Parsed.

DbSchema Version: 9.6.5 build 250106
OS: Windows
Database: Exasol

[dbschema-pro]

Thank you for reporting this.
I tested it by saving this statement to a file, and open it with 'Open SQL File' / Parse the file in DbSchema.
The primary key was reversed engineer correctly.

create or replace table test.test (
id1 integer primary key enable
);

Please install this beta:
https://dbschema.com/beta.php

Please also delete the folder .DbSchema from the user home directory. This will drop all cached settings.

Our reverse engineer is calling this:
execute script exa_toolbox.create_table_ddl(.... )
You can see the generated statement in Help / Output Logs after enabling Model / Settings / Reverse engineer / Log DDL Parser Statements.

Please let me know if this is working fine.

[ssteinhauser]

I've tried it again with the latest beta version (build 250124), but there is no change in the behavior I described initially. I've also deleted the .DbSchema directory, but it didn't change anything.

You are right, it's working better with the DDL file, but there is also one bug: when parsing the DDL with a disabled primary key, DbSchema adds a "not null" constraint to the column, which is not correct as described above.
However, initially I was talking about the reverse engineering from an Exasol database - I still experience the exact same behavior as described above.

[dbschema-pro]

Hello, We removed the not null enforce on primary key columns. A beta update will be ready tomorrow morning under: [1] https://dbschema.com/beta.php The DDL is generated using: execute script exa_toolbox.create_table_ddl(...) Best regards, Dragos Pruteanu [1] https://dbschema.com/beta.php