Hi All, Sorry for not responding to the most recent email previously.
This cqd needs to be set only at the time of table creation (i.e. DDL time). It is not necessary for DML. Thanks Suresh On Tue, Feb 2, 2016 at 2:19 PM, Eric Owhadi <[email protected]> wrote: > Oh, that looks better. I think this will work with what I am trying to do. > > Let me try it. > > Oh, and this CQD must be set only at time of table creation? Or should it > be globally set using _*MD*_.default? > > Eric > > > > *From:* Suresh Subbiah [mailto:[email protected]] > *Sent:* Tuesday, February 2, 2016 2:14 PM > *To:* [email protected] > *Subject:* Re: nullable primary key index column? > > > > Hi, > > > > Hope I did not misunderstand the question. > > > > A table can have more than one column in its key to be nullable, as long > as the cqd Anoop gave is set. All columns in the key can be nullable too. > > If the first column is nullable and there are other key columns that are > either nullable or non-nullable, then the first column can have null value > for more than 1 row, as long subsequent key columns have other values. > > For example > > >>cqd allow_nullable_unique_key_constraint 'on' ; > > > > --- SQL operation complete. > > >>create table t1 (a int, b int, primary key (a,b)) ; > > > > --- SQL operation complete. > > >>showddl t1 ; > > > > CREATE TABLE TRAFODION.JIRA.T1 > > ( > > A INT DEFAULT NULL SERIALIZED > > , B INT DEFAULT NULL SERIALIZED > > , PRIMARY KEY (A ASC, B ASC) > > ) > > ; > > > > --- SQL operation complete. > > >>insert into t1(a) values (1); > > > > --- 1 row(s) inserted. > > >>insert into t1(b) values (2) ; > > > > --- 1 row(s) inserted. > > >>insert into t1(a) values(3) ; > > > > --- 1 row(s) inserted. > > >>select * from t1 ; > > > > A B > > ----------- ----------- > > > > 1 ? > > 3 ? > > ? 2 > > > > --- 3 row(s) selected. > > > > If the table has only one key column and it is nullable, then at most only > one row can have null as is value for this column. > > > > There is an issue with inserting null value for all columns in the key as > described in JIRA 1801, which also outlines a fix suggested by Anoop. > > > > Thanks > > Suresh > > > > > > > > > > > > > > On Tue, Feb 2, 2016 at 1:29 PM, Anoop Sharma <[email protected]> > wrote: > > > > cqd ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT ‘ON’; > > > > then create table with nullable pkey col. > > > > only one null value is allowed. > > > > > > *From:* Eric Owhadi [mailto:[email protected]] > *Sent:* Tuesday, February 2, 2016 11:27 AM > *To:* [email protected] > *Subject:* nullable primary key index column? > > > > Dear Trafodioneers, > > I am wondering if it is possible to use a composite primary key with the > first column making up the primary key composite being nullable? > > If yes, is there any restriction, like only one row can be null for that > nullable column? > > Thanks in advance for the help, > Eric > > >
