Constraints

Constraints provide a way to ensure the quality of the data in the database. This section describes the constraints supported in Exasol.

When using the database migration scripts, you can choose to comment out the generated constraints to prevent them from being executed when you run the output from the script.

Primary Keys

Primary keys constraints are always generated by the Oracle migration script if they exist in Oracle. The option CREATE_PK only controls if the primary key constraints is commented out. Note that having enabled constraints on a target table might decrease loading performance.

If you disable primary keys, see ALTER TABLE (Constraints) and Verification of the Primary Key Property (PRIMARY KEY) for more details.

This example shows generated primary key definitions that are commented out:

Example

The syntax to define a primary key in Oracle and Exasol is similar, as you can see in the following examples:

-- Oracle Example
ALTER TABLE "C##DB_MIG"."SALES" ADD PRIMARY KEY("SALES_ID");

-- Exasol Example
ALTER TABLE "C##DB_MIG"."SALES" ADD PRIMARY KEY("SALES_ID");

Foreign Keys

Foreign keys are always generated by the Oracle migration script if they exist in Oracle. The option CREATE_FK only controls if the foreign key constraint is commented out. Note that having enabled constraints on a target table might decrease loading performance. Please be aware that foreign key definitions will fail if the referenced tables are not migrated as well. If you are not sure that all tables referenced by the foreign keys of your migration scope are included, set the script parameter CREATE_FK to FALSE. For more details see ALTER TABLE (Constraints) and Verification of the Foreign Key Property (FOREIGN KEY). See the output below for generated foreign key definitions, which are commented out:

Example

The Syntax to define a foreign key in Exasol is similar to Oracle, as you can see in the following example:.

--Oracle Example
ALTER TABLE "C##DB_MIG"."SALES" ADD FOREIGN KEY ("SALES_DATE") REFERENCES "C##DB_MIG"."DIM_DATE"("D_DATE");

--Exasol Example
ALTER TABLE "C##DB_MIG"."SALES" ADD FOREIGN KEY ("SALES_DATE") REFERENCES "C##DB_MIG"."DIM_DATE"("D_DATE");

Other Constraints

Constraints which are not supported in Exasol and cannot be migrated are:

  • UNIQUE Constraints
  • Column Constraints (CHECK Constraints)