[TRAFODION-1901] allow more flexible column-definition in create table ddl

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.0-incubating
• Assignee: Anoop Sharma
• Reporter: liu ming
• Created: 19/Mar/16 12:09
• Updated: 15/Sep/18 07:30
• Resolved: 19/May/16 15:30

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.