Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Constraints 2

Status
Not open for further replies.

THoey

IS-IT--Management
Jun 21, 2000
2,142
US
I have a table that is already populated with data and I need to add a constraint to it. The problem is, there are already violations to the constraint in the table, so the ALTER TABLE command fails. I tried to work around it using the following steps:

1. Create a duplicate structure of the table.
2. Copy all records from the original to the duplicate.
3. Truncate the original table.
4. Apply the constraint to the original table.
5. Copy all records back from the duplicate.

At this point, that fails due constraint violation. I was hoping it would just not load the records that violated the constraint.

I have tried writing a "find duplicates" query, but it goes out into La La land and after about an hour and a half, and I killed it off.

I guess I need help writing a query that will load from the duplicate to the original without violating the constraint. The table structures are as follows:
Code:
WALKAWAY
--------
UUID              VARCHAR2 (7)
WALKAWAY_CODE     VARCHAR2 (3)
WALKAWAY_DATE     DATE
...
The constraint is as follows:
Code:
ALTER TABLE WALKAWAY
ADD CONSTRAINT WALKAWAY_UUID_DATE_IX
PRIMARY KEY (UUID, WALKAWAY_DATE) 
USING INDEX TABLESPACE TCL_01_IDX
Any assistance would be greatly appreciated... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
Try running the utlexcpt.sql script to create an EXCEPTIONS table.

Then try creating the constraint with the following addition:

ALTER TABLE ....... exceptions into exceptions;

This will load an entry into the EXCEPTIONS table for every record in the table that violates your constraint. You can then use this information to isolate the offending rows.
 
I didn't know about this one. I did what you said, and it failed with 9771 records put into the EXCEPTIONS table. I pulled the first 10 rows from the EXCEPTIONS table, and the following is what one row looks like:
Code:
ROWID              OWNER   TABLENAME CONSTRAINT
AAAAwIAAHAAAFN9AAD EHGRIFF WALKAWAY2 WALKAWAY_UUID_DATE_IX2
What sort of ROWID is that? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
You can get the duplicates with the query

select UUID, WALKAWAY_DATE from WALKAWAY where rowid in
(select row_id from exceptions);
 
That works great. I have worked with ROWID's before, but those looked cryptic. Now, I gather, I can change that select statement to a delete script and remove them the same way? Of course, I need to verify the data can be deleted first... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
Well, after taking a closer look, I am going to say I just can't delete for each record in the EXCEPTIONS table. I am pretty sure that all of the duplicate records are in that table. So if I just run a delete like that, it is not going to just delete the dupes, but all the data that has a dupe also.

Hmmm... Any ideas? Remember, I am looking at 9771 records, so manually will be a pain... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
Be very careful when using the exceptions table to delete!. I would guess that you want to get rid of duplicates, but retain one row for each distinct combination of UUID and WALKAWAY_DATE. If you just delete the rowids specified by exceptions you will get rid of all rows containing duplicates.
 
I think we posted at the same time. BTW, thanks for the new tool. I gave you both stars to proudly wear. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
The following is a good start at identifying the rows to delete. It assumes that you don't care which duplicated row to keep. Excluded from the results set is the smallest rowid for that combination of uuid & walkaway_date.

select distinct b.rowid from WALKAWAY a, WALKAWAY b
where a.rowid in (select row_id from exceptions)
and b.rowid > a.rowid
and a.UUID = b.UUID
and a.WALKAWAY_DATE = b.WALKAWAY_DATE;
 
Karluk,

Thanks for sticking with this, but I finally got in touch with the keeper of the data. They decided that since there are only four records that are originals/duplicates from within the last couple months and this is not critical data, we can lose it. So I am going to change the select query you gave me to a delete and get rid of all of them.

Thanks again. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
Terry -
Just as a follow-up: the ROWIDs you are seeing are the "extended" ROWIDs that are new as of Oracle8. They are in base64, and were necessary due to theinclusion of object identifiers (OIDs) and Oracle's push to provide a unique OID for every object in every Oracle database in the world!
 
Wow! Is Oracle hoping to one day pull all the databases in the world together? I wouldn't put it past Ellison... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.
 
I think it's his personal vision of the Grand Unification Theory. The beauty of it is that with the odds of all the DBAs in the world getting together to compare OIDs, he can probably make the claim and nobody can deny it. If non-unique OIDs DO turn up, he can call it a bug and blame the developers (or the customers!) for fouling up.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top