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
