Prev Next

DDL Limitations

A fundamental feature of a Database Management System (DBMS) is to allow the definition of database objects via a structured language; this language is called DDL (for data definition language, or data description language). The DDL syntax of each DBMS is unique. While there are common DDL statements and keywords across all DBMSs, there are differences that require each DBMS to have its own set of DDL templates within Enterprise Architect.

This page summarizes the main limitations for each of the supported Database Management Systems.

MS Access

  • Comments cannot be applied to (or changed in) Tables, Table Columns, Table Constraints or Views, therefore Enterprise Architect ignores these differences
  • The CREATE TABLE statement does not support the definition of column defaults, therefore Enterprise Architect excludes the Default definition from all generated DDL; however, it does highlight a Default difference in the comparison logic
  • Generally object names in DDL can be enclosed in square brackets ([ ]) so that they can include spaces and other non standard characters, however the CREATE VIEW DDL statement does not support the square bracket notation; the 'Create View' DDL template replaces all spaces with underscore ('_') characters

MySQL

  • Comments can only be applied to Indexes and Unique Constraints, when the MySQL version is greater than 5.5.3
  • Comments can only be applied to Indexes and Unique Constraints when they are created, therefore changing an Index or Unique Constraint's comment causes the constraint to be dropped and recreated
  • Check Constraints are not supported; whilst the MySQL DDL engine can parse such statements, it simply ignores them
  • Comments cannot be applied to (or changed in) Views, Procedures or Functions, therefore Enterprise Architect ignores these differences

Oracle

  • Comments cannot be applied to (or changed in) Procedures, Sequences or Functions, therefore Enterprise Architect ignores these differences

PostgreSQL

  • Currently Enterprise Architect does not support function parameters, therefore any statements (COMMENT ON or DROP) that refer to a function by name will fail because they must use a combination of function name and parameters

SQL Lite

  • Constraints cannot be added to an existing Table; the Table must be dropped and created (including the new Constraint in the Create statement)
  • Comments are not supported on any object type, therefore Enterprise Architect ignores all remark differences