You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@trafodion.apache.org by Eric Owhadi <er...@esgyn.com> on 2016/02/02 20:26:53 UTC
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
RE: nullable primary key index column?
Posted by Eric Owhadi <er...@esgyn.com>.
Hi Dave
It is for cases like TPC-DS where we are dealing with snowflakes schema,
and in particular, the date dimension is of particular interest for data
partitioning.
By default, TPC-DS fact tables have composite primary keys that do not
include the date dimension. And that is fine.
Except that Trafodion horizontal partitioning (STORE BY) require that the
column in the “store by” be part of the primary key. So in order to
correctly partition the data by date, I have to set the xxx_date_sk part of
the primary key in fact tables.
But the fact data is not supposed to be “clean”, so you will find records
with null xxx_date_sk (about 4.5% in the test dataset).
So I will simply use 0 for null representation, alter the load accordingly,
and make sure we don’t have any xxx_date_sk is null/ is not null checks in
the tpc-ds queries.
Hope this clarifies?
Eric
*From:* Dave Birdsall [mailto:dave.birdsall@esgyn.com]
*Sent:* Tuesday, February 2, 2016 1:51 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: nullable primary key index column?
Hi Eric,
Just curious: What is your use case? Why do you need a nullable primary key
column?
Dave
*From:* Eric Owhadi [mailto:eric.owhadi@esgyn.com]
*Sent:* Tuesday, February 2, 2016 11:43 AM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: nullable primary key index column?
Thank you Anoop for the prompt response. It won’t help for my use case
because I have more than one null. I will therefore assign a fake value to
represent NULLs in that column.
Eric
*From:* Anoop Sharma [mailto:anoop.sharma@esgyn.com]
*Sent:* Tuesday, February 2, 2016 1:30 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: nullable primary key index column?
cqd ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT ‘ON’;
then create table with nullable pkey col.
only one null value is allowed.
*From:* Eric Owhadi [mailto:eric.owhadi@esgyn.com]
*Sent:* Tuesday, February 2, 2016 11:27 AM
*To:* user@trafodion.incubator.apache.org
*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
RE: nullable primary key index column?
Posted by Dave Birdsall <da...@esgyn.com>.
Hi Eric,
Just curious: What is your use case? Why do you need a nullable primary key
column?
Dave
*From:* Eric Owhadi [mailto:eric.owhadi@esgyn.com]
*Sent:* Tuesday, February 2, 2016 11:43 AM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: nullable primary key index column?
Thank you Anoop for the prompt response. It won’t help for my use case
because I have more than one null. I will therefore assign a fake value to
represent NULLs in that column.
Eric
*From:* Anoop Sharma [mailto:anoop.sharma@esgyn.com]
*Sent:* Tuesday, February 2, 2016 1:30 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: nullable primary key index column?
cqd ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT ‘ON’;
then create table with nullable pkey col.
only one null value is allowed.
*From:* Eric Owhadi [mailto:eric.owhadi@esgyn.com]
*Sent:* Tuesday, February 2, 2016 11:27 AM
*To:* user@trafodion.incubator.apache.org
*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
RE: nullable primary key index column?
Posted by Eric Owhadi <er...@esgyn.com>.
Thank you Anoop for the prompt response. It won’t help for my use case
because I have more than one null. I will therefore assign a fake value to
represent NULLs in that column.
Eric
*From:* Anoop Sharma [mailto:anoop.sharma@esgyn.com]
*Sent:* Tuesday, February 2, 2016 1:30 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: nullable primary key index column?
cqd ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT ‘ON’;
then create table with nullable pkey col.
only one null value is allowed.
*From:* Eric Owhadi [mailto:eric.owhadi@esgyn.com]
*Sent:* Tuesday, February 2, 2016 11:27 AM
*To:* user@trafodion.incubator.apache.org
*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
Re: nullable primary key index column?
Posted by Suresh Subbiah <su...@gmail.com>.
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 <er...@esgyn.com> 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:suresh.subbiah60@gmail.com]
> *Sent:* Tuesday, February 2, 2016 2:14 PM
> *To:* user@trafodion.incubator.apache.org
> *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 <an...@esgyn.com>
> 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:eric.owhadi@esgyn.com]
> *Sent:* Tuesday, February 2, 2016 11:27 AM
> *To:* user@trafodion.incubator.apache.org
> *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
>
>
>
RE: nullable primary key index column?
Posted by Eric Owhadi <er...@esgyn.com>.
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:suresh.subbiah60@gmail.com]
*Sent:* Tuesday, February 2, 2016 2:14 PM
*To:* user@trafodion.incubator.apache.org
*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 <an...@esgyn.com> 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:eric.owhadi@esgyn.com]
*Sent:* Tuesday, February 2, 2016 11:27 AM
*To:* user@trafodion.incubator.apache.org
*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
Re: nullable primary key index column?
Posted by Suresh Subbiah <su...@gmail.com>.
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 <an...@esgyn.com> 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:eric.owhadi@esgyn.com]
> *Sent:* Tuesday, February 2, 2016 11:27 AM
> *To:* user@trafodion.incubator.apache.org
> *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
>
RE: nullable primary key index column?
Posted by Anoop Sharma <an...@esgyn.com>.
cqd ALLOW_NULLABLE_UNIQUE_KEY_CONSTRAINT ‘ON’;
then create table with nullable pkey col.
only one null value is allowed.
*From:* Eric Owhadi [mailto:eric.owhadi@esgyn.com]
*Sent:* Tuesday, February 2, 2016 11:27 AM
*To:* user@trafodion.incubator.apache.org
*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