You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Anoop Sharma (JIRA)" <ji...@apache.org> on 2018/06/13 23:14:00 UTC
[jira] [Commented] (TRAFODION-1782) Error 1135 for a primary key
column not declared NOT NULL
[ 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)