duplicate rows, but which comlumn ?
laurent
statement, UPDATE statement, or foreign key update caused by a DELETE
statement are not valid because they would produce duplicate rows for a
table with a primary key, unique constraint, or unique index.
SQLSTATE=23505
{SQLExecute} SQLSTate: 23505
Is there a way to obtain a clearer hint ?
(which unique index is violated)
All other engines display the name of violated index, for example :
Sybase SQL Anywhere :
Erreur ODBC : [Sybase][ODBC Driver]Integrity constraint violation: index
'Model_FullName' for table 'Model' would not be unique SQLSTate: 23000
Giovanni
Roger
Laurent....
You should see the index-id ,schema and table from the output of the
message SQL0803 according to messages ref and my experience....
DB2 ? SQL0803 returns :
SQL0803N One or more values in the INSERT statement, UPDATE statement,
or foreign key update caused by a DELETE statement are not valid
because the primary key, unique constraint or unique index identified
by "<index-id>" constrains table "<table-name>" from having duplicate
rows for those columns.
If "<index-id>" is an integer value, the index name can be
obtained from SYSCAT.INDEXES by issuing the following query:
SELECT INDNAME, INDSCHEMA
FROM SYSCAT.INDEXES
WHERE IID = <index-id>
AND TABSCHEMA = 'schema'
AND TABNAME = 'table'
where schema represents the schema portion of "<table-name>" and
table represents the table name portion of "<table-name>".
/Roger
laurent
"Roger" <roger.k...@intentia.se> wrote in message
news:bc93d2c9.02070...@posting.google.com...
My error message doens not contains : identified by "<index-id>" constrains
table "<table-name>"
Are you running it against a DB2 OS390 Mainframe ?
I don't think any DB2 UDB version reported such detailed message...
Laurent
Vitaly Solntsev
> My error message doens not contains : identified by "<index-id>"
constrains
> table "<table-name>"
> Are you running it against a DB2 OS390 Mainframe ?
AFAIK, it works on all platforms. May be, it depends on ODBC-driver.
> I don't think any DB2 UDB version reported such detailed message...
Believe. Look at log: (DB2 UDB 7.2 Personal Edition at Windows 2000 Pro)
------------------------Cut
db2> CREATE TABLE primer (id INT CONSTRAINT FirstC UNIQUE NOT NULL, salary
INT CONSTRAINT SecondC CHECK (salary>1000), name CHAR(20), bonus)
db2> CREATE UNIQUE INDEX ThirdC ON primer (bonus)
DB20000I The SQL command completed successfully.
db2> INSERT INTO primer VALUES (1,15000,'Tom',150),(1,20000,'Bill',180)
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "VERTER.PRIMER" from having duplicate rows for those columns.
db2> SELECT INDNAME, INDSCHEMA FROM SYSCAT.INDEXES WHERE IID = 1 AND
TABSCHEMA = 'VERTER' AND TABNAME = 'PRIMER'
--------------------------
INDNAME INDSCHEMA
FIRSTC VERTER
Throught JDBC:
INSERT INTO primer VALUES
(3,15000,'Tom',150),(4,20000,'Bill',150)
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0803N One or
more values in the INSERT statement, UPDATE statement, or foreign key update
caused by a DELETE statement are not valid because the primary key, unique
constraint or unique index identified by "2" constrains table
"VERTER.PRIMER" from having duplicate rows for those columns.
db2> SELECT INDNAME, INDSCHEMA FROM SYSCAT.INDEXES WHERE IID = 2 AND
TABSCHEMA = 'VERTER' AND TABNAME = 'PRIMER'
--------------------------
INDNAME INDSCHEMA
THIRDC VERTER
db2> INSERT INTO primer VALUES (3,100,'Helen')
SQL0545N The requested operation is not allowed because a row does not
satisfy the check constraint "VERTER.PRIMER.SECONDC". SQLSTATE=23513
---------------------- Cut
> Laurent
Vitaly
laurent
"Vitaly Solntsev" <vsol...@gala.net> wrote in message
news:ag1bna$1jv4$1...@news.boulder.ibm.com...
> [.. skipped]
> > My error message doens not contains : identified by "<index-id>"
> constrains
> > table "<table-name>"
> > Are you running it against a DB2 OS390 Mainframe ?
>
> AFAIK, it works on all platforms. May be, it depends on ODBC-driver.
>
>
>
>
You might be right !
That the problem is on my client !
However, I think ODBC is not involved
Same problem through DB2CLP
When I connect from my client station, I got the non-explicit message
as you can see in the following session
===================================================
db2 => connect to TOTO user db2admin using xxxxxx
Database Connection Information
Database server = DB2/NT 7.2.4
SQL authorization ID = DB2ADMIN
Local database alias = TOTO
db2 => INSERT INTO primer VALUES (1,15000,'Tom',150)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because they
would produce duplicate rows for a table with a primary key, unique
constraint,
or unique index. SQLSTATE=23505
===================================================
If I do it on the server (CITRIX telnet in fact), the message is as you said
Here is the session :
===================================================
db2 => connect to toto
Database Connection Information
Database server = DB2/NT 7.2.4
SQL authorization ID = DB2ADMIN
Local database alias = TOTO
db2 => INSERT INTO primer VALUES (1,15000,'Tom',150)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "2" constrains
table "DB2ADMIN.PRIMER" from having duplicate rows for those columns.
SQLSTATE=23505
===================================================
How can I force my client station to display this full message ?
Garfield Lewis
if they are not the same level.
"laurent" <ldela...@apsydev.com> wrote in message
news:3d2455f1$0$196$4d4e...@read.news.fr.uu.net...