Anoop Sharma commented on TRAFODION-1782:

- Changes to support automatic assignment of NOT NULL NOT DROPPABLE attr for 
primary key columns in CREATE and ALTER stmts.

  - CREATE syntax examples:
 * create table t (a int primary key)
 * create table t (a int, primary key(a))
 * create table t (a int) primary key(a)
 * create table t primary key(a) as select <nullable-col> a from tsrc

  - ALTER syntax example:
 * create table t (a int)
 * alter table t add constraint pkt primary key(a)

   - ALTER with dependent objects:
 * dependent objects: views, indexes, unique/referential constraints
 * cannot add primary key if table has dependent objects
 * An error will be returned and users will need to drop the dependent objects 
before alter/add of pkey, and then recreate them afterwards.
 * cqd traf_alter_add_pkey_as_unique_constraint can be used to create a unique 
constraint when the table has dependent objects. This will not create a 
clustering primary key and will create a unique constraint/index instead. This 
is the same as explicitly issuing create constraint statement. This option 
should be  used carefully and only if really needed for some purpose. 

 - NULLABLE primary keys
 * One can extend the PRIMARY KEY clause in CREATE stmt to create a nullable 
primary key. With this option, the NOT NULL attr will not be added to the pkey 
columns. Null values will be allowed to be inserted into primary key columns.
 * Example: create table t (a int primary key nullable)
 *                 alter table t add constraint pkt primary key nullable (a)

> Error 1135 for a primary key column not declared NOT NULL
> ---------------------------------------------------------
>                 Key: TRAFODION-1782
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-1782
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 0.6 (pre-incubation)
>            Reporter: Hans Zeller
>            Assignee: Anoop Sharma
>            Priority: Minor
> When creating a table like this:
> create table t(a int primary key)
> or
> create table t(a int, primary key(a))
> Trafodion issues an error 1135 (Clustering key column ID must be assigned a 
> NOT NULL NOT DROPPABLE constraint.). It would be better (and ANSI SQL 
> compliant) if that constraint would be added implicitly, without generating 
> an error.

This message was sent by Atlassian JIRA

Reply via email to