[jira] [Created] (TRAFODION-1901) allow more flexible column-definition in create table ddl

["liu ming (JIRA)" <ji...@apache.org>]

liu ming created TRAFODION-1901:
-----------------------------------

             Summary: allow more flexible column-definition in create table ddl
                 Key: TRAFODION-1901
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1901
             Project: Apache Trafodion
          Issue Type: Improvement
            Reporter: liu ming


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.





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)