The problem is what return code would the statement have if the values are not unique?
Admin tools may well assume negative return codes to be failure.
On the other hand a positive value would not indicate the new index was not unique.
I suppose something could be added to sqlca.sqlwarn but that may not be checked by tools.
Perhaps add a new sql clause to ignore duplicates?
Sybase
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/X65321.htm ignore_dup_key - added to create index statement, does not work with existing duplicate values
""any attempt to insert a duplicate key is canceled with an error message. After the cancellation, any transaction that was active at the time may continue as though the update or insert had never taken place.""
SQL Server
https://sqlperformance.com/2019/04/sql-performance/ignore_dup_key-slower-clustered-indexes WITH (IGNORE_DUP_KEY = ON) - Added to index or constraint definition
"A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail."
Oracle
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#CHDEGDDG "The
IGNORE_ROW_ON_DUPKEY_INDEX
hint applies only to single-table
INSERT
operations. It is not supported for
UPDATE
,
DELETE
,
MERGE
, or multitable insert operations....When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row."
MySQl
https://smallbusiness.chron.com/duplicate-ignore-mysql-inserts-28260.html https://dev.mysql.com/doc/refman/8.0/en/insert.html https://dev.mysql.com/doc/refman/8.0/en/replace.html Additional clauses on insert statement.
"INSERT IGNORE will insert rows in the same way as INSERT, but with the exception that it will ignore rows with duplicate values and continue execution without creating an error."
"INSERT ... ON DUPLICATE KEY UPDATE will insert any non-duplicate rows as normal. However, when it encounters a duplicate row, it will perform an UPDATE on the original row"
"REPLACE works the same as INSERT, except that when it encounters a duplicate row, it deletes the original row and then continues with the insert. "
Vendors cannot agreed on how to do this!!
------------------------------
David Williams
------------------------------
Original Message:
Sent: Tue June 30, 2020 01:55 PM
From: Mark Jalkiewicz
Subject: Adding primary to a table having duplicate rows
another possibility is to have create unique index stmts default to a non-unique index if the uniqueness fails. That way you get the benefit of having a defined index in place.
Original Message:
Sent: 6/30/2020 1:04:00 PM
From: Art Kagel
Subject: RE: Adding primary to a table having duplicate rows
David:
Worth a try. HOwever, I'm not sure whether that conversion would not be a big deal to implement. Structurally a UNIQUE index is different from an index that was not declared as UNIQUE but that happens to have unique rows. A UNIQUE index places the one ROWID for each key in the leaf node of the index. A duplicate index places a pointer to a page containing rowids that contain that key into the leaf node. That makes a unique index more efficient to search than a duplicate index that happens to have unique keys in it.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Original Message:
Sent: 6/30/2020 11:15:00 AM
From: David Williams
Subject: RE: Adding primary to a table having duplicate rows
I would make sure there is a non-unique index on the key columns first.
Then
create new table with key cols from old table
select <keycols> from table group by <keycols> having count(*) > 1;
Fix the rows with duplicates.
All,
I did have an RFE idea - a command to alter a non-unique index to become a unique index
i.e. lock index, index scan for dups, if none found make index unique.
Does that sounds useful?
------------------------------
David Williams
Original Message:
Sent: Tue June 30, 2020 10:44 AM
From: Art Kagel
Subject: Adding primary to a table having duplicate rows
You cannot have a primary key or unique key or even a UNIQUE index on a table with rows that have duplicate key column values. The only thing you can do is to remove the dups or change their key column values. Here are some ways to do that. Here's one:
SELECT a.key_col1, a.key_col2, a.rowid
FROM dups_table AS a, dupes_table AS b
WHERE a.key_col1 = b.key_col1
AND a.key_col2 = b.key_col2
AND a.rowid > b.rowid;
This will select all but the "first" row of a duplicated key set and you can delete those rows by rowid. If you need to be more selective, then change the rowid filter to != and add a DISTINCT clause to the projection list then you can select the entire row (or just add those columns you will use to decide which rows to keep and which to delete) to the projection.
If the table is partitioned you cannot use ROWID unless the table was created WITH ROWID, but you can use "ifx_row_id" instead which is a string that contains the partition number and rowid (ex: "1048816:3585").
Art
.
Art S. Kagel, President and Principal Consultant
ASK Database Management
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Original Message:
Sent: 6/30/2020 5:09:00 AM
From: Piyush Sachan
Subject: Adding primary to a table having duplicate rows
*Problem:* I have a table which has multiple duplicate records since very beginning, now the table is in production and we cannot remove those duplicate records because they are partially duplicate. And we cannot also move those records to new table as we don't know in how many 4GLs program table have been used.
*Goal:* Adding a primary key on the table having multiple duplicate rows
*Observation:* I have read few solutions on Informix online manual that we can set *Primary Key* with *DISABLED* option
Then we can start *VIOLATION* and set *ENABLED* on created Primary Key Constraints but using this option I got error says cannot set Primary Key because of duplicate records.
Can u please tell what I am doing wrong?
Or, their is no such concept existing in Informix that we can set Primary Key for a table having duplicate records from now onwards.
------------------------------
Piyush Sachan
CM/FGK
------------------------------
#Informix