[ 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)