[
https://issues.apache.org/jira/browse/TRAFODION-1782?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16511773#comment-16511773
]
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
(v7.6.3#76005)
