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 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 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)