You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Suman N <Su...@curamsoftware.com> on 2007/10/17 12:46:48 UTC
Getting "Constraints 'SQL071017145943950' and 'SQL071017145906390' have the same set of columns, which is not allowed." while adding a Unique/primary key constraint to the table
Hi,
I have executed the following queries on a table in the given order and
I am getting the error mentioned below.
I have created a table and then created an unique index on two columns
for the table.
I have used the same two columns to add a primary key for the table and
then used a query to set the two columns with unique key constraint.
I. CREATE TABLE XSLTEMPLATE_TEMP(TEMPLATEID BIGINT
not null, LOCALEIDENTIFIER CHARACTER(5) not null, TEMPLATENAME
CHARACTER(100), LATESTVERSION INT not null, CHECKEDOUT CHARACTER(1) not
null, CHECKEDOUTBY CHARACTER(30), CHECKEDOUTVERSION INT not null,
CHECKEDOUTTIME TIMESTAMP, COCOMMENT VARCHAR(300), TEMPLATEFILE
VARCHAR(300), TEMPLATETYPE CHARACTER(10), TEMPLATEIDCODE CHARACTER(10),
EDITABLE CHARACTER(1) not null, RELATESTO CHARACTER(10), LASTWRITTEN
TIMESTAMP);
II. CREATE UNIQUE INDEX XSLTEMPLATE_TEMP ON
XSLTEMPLATE_TEMP(TEMPLATEID, LOCALEIDENTIFIER);
III. ALTER TABLE XSLTEMPLATE_TEMP ADD CONSTRAINT
XSLTEMPLATE_TEMP PRIMARY KEY(TEMPLATEID, LOCALEIDENTIFIER);
IV. ALTER TABLE XSLTEMPLATE_TEMP ADD UNIQUE
(LOCALEIDENTIFIER, TEMPLATEID);
When the last query to add a unique key constraint on the table, I am
getting the following error
ERROR 42Z93: Constraints 'SQL071017145943950' and 'SQL071017145906390'
have the same set of columns, which is not allowed.
Either of the query iii or query iv alone could be executed
successfully. I was not able to execute both these queries
Can anyone please explain why I am getting this error and also suggest
what needs to be done to avoid this error?
Thanks & Regards,
Suman.N
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else
is unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to be taken in reliance
on it, is prohibited and may be unlawful. If you are not the intended
addressee please contact the sender and dispose of this e-mail. Thank you.
Re: Getting
"Constraints 'SQL071017145943950' and 'SQL071017145906390' have the same set
of columns, which is not allowed." while adding a Unique/primary key
constraint to the table
Posted by Jørgen Løland <Jo...@Sun.COM>.
Hi,
I don't know whether this is allowed in other databases, but defining
the primary key unique would in any case be redundant since that is
implied by the primary key constraint.
Regards,
Jørgen
Suman N wrote:
> Hi Jørgen,
>
> Thank you very much for the speedy reply.
> But the same queries seem to work when I execute them in DB2 and Oracle. Is this behavior only subjective to Derby database?
>
> Thanks & Regards,
> Suman
>
> -----Original Message-----
> From: Jorgen.Loland@Sun.COM [mailto:Jorgen.Loland@Sun.COM]
> Sent: Wednesday, October 17, 2007 5:23 PM
> To: Derby Discussion
> Subject: Re: Getting "Constraints 'SQL071017145943950' and 'SQL071017145906390' have the same set of columns, which is not allowed." while adding a Unique/primary key constraint to the table
>
> Hi Suman,
>
> What you're doing here is to first make the attribute pair
> (LOCALEIDENTIFIER, TEMPLATEID) a primary key, and then give the same
> attribute pair a unique constraint. The primary key constraint
> guarantees uniqueness, and step IV is therefore not allowed and results
> in an exception saying that the primary key attributes cannot have a
> unique constraint at the same time.
>
> Hope this answered your question.
>
> Regards,
> Jørgen Løland
>
>
> Suman N wrote:
>> Hi,
>>
>> I have executed the following queries on a table in the given order and
>> I am getting the error mentioned below.
>>
>> I have created a table and then created an unique index on two columns
>> for the table.
>>
>> I have used the same two columns to add a primary key for the table and
>> then used a query to set the two columns with unique key constraint.
>>
>>
>> I. CREATE TABLE XSLTEMPLATE_TEMP(TEMPLATEID BIGINT
>> not null, LOCALEIDENTIFIER CHARACTER(5) not null, TEMPLATENAME
>> CHARACTER(100), LATESTVERSION INT not null, CHECKEDOUT CHARACTER(1) not
>> null, CHECKEDOUTBY CHARACTER(30), CHECKEDOUTVERSION INT not null,
>> CHECKEDOUTTIME TIMESTAMP, COCOMMENT VARCHAR(300), TEMPLATEFILE
>> VARCHAR(300), TEMPLATETYPE CHARACTER(10), TEMPLATEIDCODE CHARACTER(10),
>> EDITABLE CHARACTER(1) not null, RELATESTO CHARACTER(10), LASTWRITTEN
>> TIMESTAMP);
>>
>>
>> II. CREATE UNIQUE INDEX XSLTEMPLATE_TEMP ON
>> XSLTEMPLATE_TEMP(TEMPLATEID, LOCALEIDENTIFIER);
>>
>>
>> III. ALTER TABLE XSLTEMPLATE_TEMP ADD CONSTRAINT
>> XSLTEMPLATE_TEMP PRIMARY KEY(TEMPLATEID, LOCALEIDENTIFIER);
>>
>> IV. ALTER TABLE XSLTEMPLATE_TEMP ADD UNIQUE
>> (LOCALEIDENTIFIER, TEMPLATEID);
>>
>> When the last query to add a unique key constraint on the table, I am
>> getting the following error
>>
>> ERROR 42Z93: Constraints 'SQL071017145943950' and 'SQL071017145906390'
>> have the same set of columns, which is not allowed.
>>
>> Either of the query iii or query iv alone could be executed
>> successfully. I was not able to execute both these queries
>>
>> Can anyone please explain why I am getting this error and also suggest
>> what needs to be done to avoid this error?
>>
>> Thanks & Regards,
>>
>> Suman.N
>
>
> The information in this email is confidential and may be legally privileged.
> It is intended solely for the addressee. Access to this email by anyone else
> is unauthorized. If you are not the intended recipient, any disclosure,
> copying, distribution or any action taken or omitted to be taken in reliance
> on it, is prohibited and may be unlawful. If you are not the intended
> addressee please contact the sender and dispose of this e-mail. Thank you.
>
--
Jørgen Løland
RE: Getting "Constraints 'SQL071017145943950' and 'SQL071017145906390' have the same set of columns, which is not allowed." while adding a Unique/primary key constraint to the table
Posted by Suman N <Su...@curamsoftware.com>.
Hi Jørgen,
Thank you very much for the speedy reply.
But the same queries seem to work when I execute them in DB2 and Oracle. Is this behavior only subjective to Derby database?
Thanks & Regards,
Suman
-----Original Message-----
From: Jorgen.Loland@Sun.COM [mailto:Jorgen.Loland@Sun.COM]
Sent: Wednesday, October 17, 2007 5:23 PM
To: Derby Discussion
Subject: Re: Getting "Constraints 'SQL071017145943950' and 'SQL071017145906390' have the same set of columns, which is not allowed." while adding a Unique/primary key constraint to the table
Hi Suman,
What you're doing here is to first make the attribute pair
(LOCALEIDENTIFIER, TEMPLATEID) a primary key, and then give the same
attribute pair a unique constraint. The primary key constraint
guarantees uniqueness, and step IV is therefore not allowed and results
in an exception saying that the primary key attributes cannot have a
unique constraint at the same time.
Hope this answered your question.
Regards,
Jørgen Løland
Suman N wrote:
> Hi,
>
> I have executed the following queries on a table in the given order and
> I am getting the error mentioned below.
>
> I have created a table and then created an unique index on two columns
> for the table.
>
> I have used the same two columns to add a primary key for the table and
> then used a query to set the two columns with unique key constraint.
>
>
> I. CREATE TABLE XSLTEMPLATE_TEMP(TEMPLATEID BIGINT
> not null, LOCALEIDENTIFIER CHARACTER(5) not null, TEMPLATENAME
> CHARACTER(100), LATESTVERSION INT not null, CHECKEDOUT CHARACTER(1) not
> null, CHECKEDOUTBY CHARACTER(30), CHECKEDOUTVERSION INT not null,
> CHECKEDOUTTIME TIMESTAMP, COCOMMENT VARCHAR(300), TEMPLATEFILE
> VARCHAR(300), TEMPLATETYPE CHARACTER(10), TEMPLATEIDCODE CHARACTER(10),
> EDITABLE CHARACTER(1) not null, RELATESTO CHARACTER(10), LASTWRITTEN
> TIMESTAMP);
>
>
> II. CREATE UNIQUE INDEX XSLTEMPLATE_TEMP ON
> XSLTEMPLATE_TEMP(TEMPLATEID, LOCALEIDENTIFIER);
>
>
> III. ALTER TABLE XSLTEMPLATE_TEMP ADD CONSTRAINT
> XSLTEMPLATE_TEMP PRIMARY KEY(TEMPLATEID, LOCALEIDENTIFIER);
>
> IV. ALTER TABLE XSLTEMPLATE_TEMP ADD UNIQUE
> (LOCALEIDENTIFIER, TEMPLATEID);
>
> When the last query to add a unique key constraint on the table, I am
> getting the following error
>
> ERROR 42Z93: Constraints 'SQL071017145943950' and 'SQL071017145906390'
> have the same set of columns, which is not allowed.
>
> Either of the query iii or query iv alone could be executed
> successfully. I was not able to execute both these queries
>
> Can anyone please explain why I am getting this error and also suggest
> what needs to be done to avoid this error?
>
> Thanks & Regards,
>
> Suman.N
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else
is unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to be taken in reliance
on it, is prohibited and may be unlawful. If you are not the intended
addressee please contact the sender and dispose of this e-mail. Thank you.
Re: Getting
"Constraints 'SQL071017145943950' and 'SQL071017145906390' have the same set
of columns, which is not allowed." while adding a Unique/primary key
constraint to the table
Posted by Jørgen Løland <Jo...@Sun.COM>.
Hi Suman,
What you're doing here is to first make the attribute pair
(LOCALEIDENTIFIER, TEMPLATEID) a primary key, and then give the same
attribute pair a unique constraint. The primary key constraint
guarantees uniqueness, and step IV is therefore not allowed and results
in an exception saying that the primary key attributes cannot have a
unique constraint at the same time.
Hope this answered your question.
Regards,
Jørgen Løland
Suman N wrote:
> Hi,
>
> I have executed the following queries on a table in the given order and
> I am getting the error mentioned below.
>
> I have created a table and then created an unique index on two columns
> for the table.
>
> I have used the same two columns to add a primary key for the table and
> then used a query to set the two columns with unique key constraint.
>
>
> I. CREATE TABLE XSLTEMPLATE_TEMP(TEMPLATEID BIGINT
> not null, LOCALEIDENTIFIER CHARACTER(5) not null, TEMPLATENAME
> CHARACTER(100), LATESTVERSION INT not null, CHECKEDOUT CHARACTER(1) not
> null, CHECKEDOUTBY CHARACTER(30), CHECKEDOUTVERSION INT not null,
> CHECKEDOUTTIME TIMESTAMP, COCOMMENT VARCHAR(300), TEMPLATEFILE
> VARCHAR(300), TEMPLATETYPE CHARACTER(10), TEMPLATEIDCODE CHARACTER(10),
> EDITABLE CHARACTER(1) not null, RELATESTO CHARACTER(10), LASTWRITTEN
> TIMESTAMP);
>
>
> II. CREATE UNIQUE INDEX XSLTEMPLATE_TEMP ON
> XSLTEMPLATE_TEMP(TEMPLATEID, LOCALEIDENTIFIER);
>
>
> III. ALTER TABLE XSLTEMPLATE_TEMP ADD CONSTRAINT
> XSLTEMPLATE_TEMP PRIMARY KEY(TEMPLATEID, LOCALEIDENTIFIER);
>
> IV. ALTER TABLE XSLTEMPLATE_TEMP ADD UNIQUE
> (LOCALEIDENTIFIER, TEMPLATEID);
>
> When the last query to add a unique key constraint on the table, I am
> getting the following error
>
> ERROR 42Z93: Constraints 'SQL071017145943950' and 'SQL071017145906390'
> have the same set of columns, which is not allowed.
>
> Either of the query iii or query iv alone could be executed
> successfully. I was not able to execute both these queries
>
> Can anyone please explain why I am getting this error and also suggest
> what needs to be done to avoid this error?
>
> Thanks & Regards,
>
> Suman.N
RE: Getting "Constraints 'SQL071017145943950' and 'SQL071017145906390' have the same set of columns, which is not allowed." while adding a Unique/primary key constraint to the table
Posted by Suman N <Su...@curamsoftware.com>.
Hi Craig,
Thank you very much for the response. This made it very clear.
Thanks,
Suman
________________________________
From: Craig.Russell@Sun.COM [mailto:Craig.Russell@Sun.COM]
Sent: Wednesday, October 17, 2007 9:03 PM
To: Derby Discussion
Subject: Re: Getting "Constraints 'SQL071017145943950' and
'SQL071017145906390' have the same set of columns, which is not
allowed." while adding a Unique/primary key constraint to the table
Hi Suman,
You do not get any extra value from II or IV. It's sufficient to have
the primary key on the two columns. That gives you uniqueness for the
columns and so an extra uniqueness constraint is redundant, and, well,
erroneous.
Craig
On Oct 17, 2007, at 3:46 AM, Suman N wrote:
Hi,
I have executed the following queries on a table in the given order and
I am getting the error mentioned below.
I have created a table and then created an unique index on two columns
for the table.
I have used the same two columns to add a primary key for the table and
then used a query to set the two columns with unique key constraint.
CREATE TABLE XSLTEMPLATE_TEMP(TEMPLATEID BIGINT not null,
LOCALEIDENTIFIER CHARACTER(5) not null, TEMPLATENAME CHARACTER(100),
LATESTVERSION INT not null, CHECKEDOUT CHARACTER(1) not null,
CHECKEDOUTBY CHARACTER(30), CHECKEDOUTVERSION INT not null,
CHECKEDOUTTIME TIMESTAMP, COCOMMENT VARCHAR(300), TEMPLATEFILE
VARCHAR(300), TEMPLATETYPE CHARACTER(10), TEMPLATEIDCODE CHARACTER(10),
EDITABLE CHARACTER(1) not null, RELATESTO CHARACTER(10), LASTWRITTEN
TIMESTAMP);
CREATE UNIQUE INDEX XSLTEMPLATE_TEMP ON XSLTEMPLATE_TEMP(TEMPLATEID,
LOCALEIDENTIFIER);
ALTER TABLE XSLTEMPLATE_TEMP ADD CONSTRAINT XSLTEMPLATE_TEMP PRIMARY
KEY(TEMPLATEID, LOCALEIDENTIFIER);
ALTER TABLE XSLTEMPLATE_TEMP ADD UNIQUE (LOCALEIDENTIFIER, TEMPLATEID);
When the last query to add a unique key constraint on the table, I am
getting the following error
ERROR 42Z93: Constraints 'SQL071017145943950' and 'SQL071017145906390'
have the same set of columns, which is not allowed.
Either of the query iii or query iv alone could be executed
successfully. I was not able to execute both these queries
Can anyone please explain why I am getting this error and also suggest
what needs to be done to avoid this error?
Thanks & Regards,
Suman.N
The information in this email is confidential and may be legally
privileged.
It is intended solely for the addressee. Access to this email by anyone
else
is unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to be taken in
reliance
on it, is prohibited and may be unlawful. If you are not the intended
addressee please contact the sender and dispose of this e-mail. Thank
you.
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else
is unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to be taken in reliance
on it, is prohibited and may be unlawful. If you are not the intended
addressee please contact the sender and dispose of this e-mail. Thank you.
Re: Getting
"Constraints 'SQL071017145943950' and 'SQL071017145906390' have the same set
of columns, which is not allowed." while adding a Unique/primary key
constraint to the table
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Suman,
You do not get any extra value from II or IV. It's sufficient to have
the primary key on the two columns. That gives you uniqueness for the
columns and so an extra uniqueness constraint is redundant, and,
well, erroneous.
Craig
On Oct 17, 2007, at 3:46 AM, Suman N wrote:
> Hi,
>
>
>
>
>
> I have executed the following queries on a table in the given order
> and I am getting the error mentioned below.
>
> I have created a table and then created an unique index on two
> columns for the table.
>
> I have used the same two columns to add a primary key for the table
> and then used a query to set the two columns with unique key
> constraint.
>
>
>
>
>
>
>
>
>
> I. CREATE TABLE XSLTEMPLATE_TEMP(TEMPLATEID
> BIGINT not null, LOCALEIDENTIFIER CHARACTER(5) not null,
> TEMPLATENAME CHARACTER(100), LATESTVERSION INT not null, CHECKEDOUT
> CHARACTER(1) not null, CHECKEDOUTBY CHARACTER(30),
> CHECKEDOUTVERSION INT not null, CHECKEDOUTTIME TIMESTAMP, COCOMMENT
> VARCHAR(300), TEMPLATEFILE VARCHAR(300), TEMPLATETYPE CHARACTER
> (10), TEMPLATEIDCODE CHARACTER(10), EDITABLE CHARACTER(1) not null,
> RELATESTO CHARACTER(10), LASTWRITTEN TIMESTAMP);
>
>
>
>
>
> II. CREATE UNIQUE INDEX XSLTEMPLATE_TEMP ON
> XSLTEMPLATE_TEMP(TEMPLATEID, LOCALEIDENTIFIER);
>
>
>
>
>
> III. ALTER TABLE XSLTEMPLATE_TEMP ADD CONSTRAINT
> XSLTEMPLATE_TEMP PRIMARY KEY(TEMPLATEID, LOCALEIDENTIFIER);
>
>
>
>
>
> IV. ALTER TABLE XSLTEMPLATE_TEMP ADD UNIQUE
> (LOCALEIDENTIFIER, TEMPLATEID);
>
>
>
>
>
>
>
> When the last query to add a unique key constraint on the table, I
> am getting the following error
>
>
>
>
>
> ERROR 42Z93: Constraints 'SQL071017145943950' and
> 'SQL071017145906390' have the same set of columns, which is not
> allowed.
>
>
>
>
>
> Either of the query iii or query iv alone could be executed
> successfully. I was not able to execute both these queries
>
>
>
> Can anyone please explain why I am getting this error and also
> suggest what needs to be done to avoid this error?
>
>
>
>
>
> Thanks & Regards,
>
> Suman.N
>
> The information in this email is confidential and may be legally
> privileged.
> It is intended solely for the addressee. Access to this email by
> anyone else
> is unauthorized. If you are not the intended recipient, any
> disclosure,
> copying, distribution or any action taken or omitted to be taken in
> reliance
> on it, is prohibited and may be unlawful. If you are not the intended
> addressee please contact the sender and dispose of this e-mail.
> Thank you.
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!