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