Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-1901

allow more flexible column-definition in create table ddl

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.0-incubating
    • Component/s: None
    • Labels:
      None

      Description

      Current Trafodion DDL syntax for CREATE TABLE is very strict to ANSI standard about column-definition.
      column-definition is:

      column data-type
      [DEFAULT default | NO DEFAULT]
      [[CONSTRAINT constraint-name] column-constraint]

      So constraint like 'NOT NULL' must follow the DEFAULT descriptor. In many other databases, this is allowed. So if Trafodion make this more flexible, it will help database migration.

      Here is a test case:

      CREATE TABLE TABLETEST1
      (MARK_ID SMALLINT NOT NULL,
      BEGIN_TIME DATE NOT NULL DEFAULT date'2008-01-01',
      END_TIME DATE NOT NULL DEFAULT date'2018-01-01',
      ACTIVE_FLAG SMALLINT,
      MARK_NAME VARCHAR(20),
      DESC_TXT VARCHAR(80),
      primary key(MARK_ID, BEGIN_TIME, END_TIME)
      );

          • ERROR[15001] A syntax error occurred at or before:
            CREATE TABLE TABLETEST (MARK_ID SMALLINT NO
            T NULL, BEGIN_TIME DATE NOT NULL DEFAULT date'2008-01-01', END
            ^ (134 characters from start of SQL statement)
          • ERROR[8822] The statement was not prepared.

      Trafodion support of above DDL will greatly simplify the database migration from other databases into Trafodion.

        Attachments

          Activity

            People

            • Assignee:
              anoopsharma Anoop Sharma
              Reporter:
              ovis_poly liu ming
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: