This is exactly what I wanted - this has numerous application throughout our system. I really like the fact that this automatically accomodates any additional constraints that might be added in the future... Thanks!

What is the Relationship Between UNIQUE Index and UNIQUE CONSTRAINT? How does UNIQUE INDEX work on a COLUMN ( having no UNIQUE CONSTRAINT)?
NOTE,I AM NOT ABLE TO POST A QUESTION! WHAT SHOULD I DO?

Tom,
I like your suggestion of issuing a statement within a savepoint, and I can imagine several places where I can use that approach - thanks!

However, would your approach to this problem work if all FK constraints used the "on delete cascade" clause? I believe then that the delete statement would not raise the exception, and that SQL%ROWCOUNT would equal just 1 even if multiple child records were deleted. So how would you know if child records existed or not?

Thanks, Kurt

Tom,
In the same way that Tony is trying to catch FK violations, we're trying to catch unique constraint violations, and may also wish to catch check constraint violations at a later time. When I say catch, I mean detect that a violation occurred (or would occur?) and return a *meaningful* error message/code.

Suppose I have a personnel table with a sequence-driven PK as well as a unique constraint on the SSN column. If a client attempts to insert a new record which includes a duplicate SSN, a DUP_VAL_ON_INDEX exception is raised. But the same exception is also raised if (by error) the client attempts to insert a record which includes a duplicate PK value. Rather than return a message/code stating "Duplicate value, but I don't know which column.", I'd like to return "Duplicate PK value" or "Duplicate SSN value", depending on the constraint that was actually violated.

One approach (the "pre-test" or "pro-active" approach) is to first issue a query looking for the potentially offending values, and if no record is found, issue the insert statement. But I don't like that approach much because it's doing twice the work for a presumably rare occurrence.

I've used another ("re-active") approach where the DUP_VAL_ON_INDEX handler block searches SQLERRM for the literal name of the unique constraint - something like:

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
IF INSTR(SQLERRM,'T_PK')>0 THEN
RAISE_APPLICATION_ERROR(-20000,'Duplicate PK value');
ELSIF INSTR(SQLERRM,'T_UNQ_SSN')>0 THEN
RAISE_APPLICATION_ERROR(-20001,'Duplicate SSN value');
ELSE
RAISE;
END IF;

As long as we can control the constraint names at the time of DDL execution (the names must be unambiguous such that the instr() call won't return false positives), this approach works fine, but other developers are resistant to hardcoding object names in the code.

Can you recommend an alternate approach that is more elegant than this one?

Thanks, Kurt

Hey Kurt,

I’ll get a stab at this one … <description>Table with primary and unique constraints … on insert, would like to report on the columns violating the unique constraints.</description>

First of all some observations regarding the context:
1. You describe a scenario where one record is inserted at a time … for batch operations ( “INSERT INTO … SELECT FROM …” or BULK inserts) it would be rather complicated! So we won’t go there.

2. You said <quote>sequence driven PK</quote> … well, there is no point in having a sequence for a synthetic primary key if some people use it and some don’t … I would say a violation of the PK is a bug (cause somebody coded an insert without using the sequence or did an INSERT … SEQ.NEXTVAL and then INSERT … SEQ.CURRVAL … hey, anything is possible out there!) … where a violation of the unique constraint is likely an application error to be expected in a multi-user environment. I like bugs to literally explode in somebody’s face during development or QA … I program for the application errors since they are part of normal processing. It all depends on how you _publish_ those ORA-20000 and ORA-20001 in your code snippet. If the caller captures both Oracle errors and pops-up a window with the error text then you, in fact, have buried a bug even deeper. The end-user will think “I probably inserted something that conflicted with an existent record … hence, try again or later or never or whatever” … but the user’s view of the record does not include the PK value (the PK is synthetic, artificial, made up …) … the real payload of the record is probably OK but it cannot go in because of the PK bug.

3. Uniqueness is enforced by unique indexes (physical structures) … the Oracle primary key and unique constraints are just declarations (on top of unique indexes of course). I’m just saying that the existence of a unique constraint implies the existence of a unique index with the same name … but the reverse is not true.

In the light of #2 and #3 above I’ll rephrase the problem (still relevant) as …
<description>Table with multiple unique indexes … on insert (or update) would like to name the columns with the offending values</description>

4. Regarding the approach … you called them “pro-active” (when the steps are: verify and maybe insert) and “re-active” (when the steps are: insert and maybe deal with errors); I prefer to call them the “pessimist” and “optimist” approaches (“pro-active” usually has this positive connotation to it which I don’t think is granted in this case … or maybe is just my poor command of the English language!). In any case, as you said about the “pro-active” approach: <quote> I don't like that approach much because it's doing twice the work for a presumably rare occurrence</quote>. Actually, it is more than that … you still have to code the exception part … the fact that you passed the _verification_ part does not guarantee the insert will go through without unique violations. Consider this: table with two numeric columns, unique indexes on each, table has record (1,1) … one session inserts (2,2) but doesn’t commit yet … another session verifies for (2,2) doesn’t find it and proceeds to execute the insert, it hangs till the other session commits and then fails with ORA-00001. Even if you have channeled all inserts through a small transaction block doing select-to-verify and insert and commit … two sessions executing the same block to insert the same value will race each other … they’ll both pass the _verification_ part, but the one getting blocked out will fail with ORA-00001. Remote possibility … but nonetheless a possibility. Of course, if the select and insert and commit are not tightly packed (like when they are separate SQL calls from a client over network … or with other code in between) the likelihood of the conflict increases.

Now, let us go to your actual question regarding the snippet of code for the exception part of the “re-active” approach.

Regarding this <quote> As long as we can control the constraint names at the time of DDL execution (the names must be unambiguous such that the instr() call won't return false positives), this approach works fine</quote>. Well, the false positive can be dealt with fairly easy … consider the pattern of the error:
“ORA-00001: unique constraint (GABE.UK) violated” … here “GABE” is the owner and “UK” is the name of the unique index … rather than doing INSTR(SQLERRM, ‘UK’) … do INSTR(SQLERRM,’.UK)’ … that is, include the dot and the bracket. This will eliminate the false positive when one has 2 unique indexes named ‘UK’ and ‘UK2’.

Pass this issue though , I would say I would be on the side of those resistant to hardcoding index names in the code … of course, one could not be “too opposed” since it would imply never using hints referencing an index. Nonetheless, I would try not to hardcode if at all possible (hey, it is)!

Here are my observations:
A. Kudos for not forgetting that “ELSE RAISE;”

B. If your record has values violating both unique indexes then you’ll still report just on one index (after all the effort to pinpoint the offending column)! Which one? Well, on my tests it seemed Oracle verifies in the order in which the unique indexes were created. Maybe Tom knows the internal workings … but then again, is not that relevant after all.

C. You are already hardcoding (hence know) the object you’re inserting into … hence you could query the dictionary for UNIQUE indexes on it (ALL_INDEXES, ALL_IND_COLUMNS) … hence you could eliminate the hardcoding of the index names. More, by looping through the indexes you eliminate the need for future code maintenance when yet another unique index gets added. Of course, it gets a bit more complicated if the object you’re referencing is not a table your schema owns, or it is a view or synonym for a remote object … this is why I said ALL_INDEXES and not USER_INDEXES … you may even have to get the synonym definition and access the remote dictionary, etc … the fun part.

D. You may want to include the actual values together with the column names in the error messages … the end-user may not recognize the column names since the GUI may label them differently … the values could help along.

Well, this is it … hope it makes sense.

Gabriel

Gabriel,
Thanks for the great feedback - it must have taken you a long time to analyze the issue and formulate a response! I've paraphrased your comments below, and added some follow-on information.

1. "Approach is not appropriate for bulk inserts." Yes - this approach is only useful for single record inserts and updates. In support of a database-driven web application, I'm keying on single record operations, and bulk operations would need to handle errors in a different fashion.

2. "PK violation is a bug." Absolutely - encountering a PK violation is something that should only happen during initial development, or as the result of a faulty change to existing code. The goal of the approach is to provide sufficient diagnostic information such that the user or support staff can figure out what to do next. If a PK violation occurs during production use, then the user is powerless to fix it once they receive a "Duplicate PK value" error message. On the other hand, if it's a user-entered data issue, then the user can change the data they're entering and try again, or abort completely.

3. "We should refer to this as a unique index issue rather than a unique constraint issue." Actually (according to this site), unique constraints can be enforced by non-unique indexes, but that might not matter here. We're explicitly issuing a statement like "alter table personnel add constraint personnel_unq1 unique(ssn);". As long as that constraint creation statement succeeds, I *think* that Oracle will mention that constraint name in SQLERRM when that constraint is violated, no matter how the constraint was implemented under the covers. But you raise a good point, and I should test what Oracle puts in SQLERRM when a constraint does "hijack" an existing index rather than creating a new index.

4. "Exception handler must be coded the same whether optimistic or pessimistic approaches are used in order to handle a race condition." As you suggest, the proper way to code a pessimistic approach is to tightly pack the 2 statements together into a single PL/SQL routine. (Although we never commit in PL/SQL - that's the client's responsibility.) But if I were to code a pessimistic approach, I'd consider the risk of a race condition to be so minimal that I'd just let the exception bubble up in its raw form. I'm not saying the exception would be masked, or hidden, or ignored - just not massaged in any way for the benefit of the user. It's only in the optimistic approach where I'd expend effort to provide a context-sensitive error message.

5. "Use '.<constraint_name>)' as the target pattern for instr()." That's a good suggestion. Our syntax for a unique constraint name is actually <table_name>_unq<sequence> where <sequence> is a 1-digit base-1 incrementing number. If we use this syntax, and the specified exception handler, a false positive will certainly occur if we have more than 9 unique constraints, constraint PERSONNEL_UNQ10 was raised, and we searched SQLERRM for "PERSONNEL_UNQ1" before searching for "PERSONNEL_UNQ10". Your target pattern syntax is better, so I'll switch to that. It will work as long as the format of SQLERRM doesn't change!

6. "Avoid hardcoding object names in strings." I agree. On the other hand, we're *in* PL/SQL referencing specific objects names already, so it's not that much of a stretch to reference an object name in a string. The real issue is "tight" versus "loose" coupling - there are now 2 places in the code that must always be kept synchronized. If the constraint name is changed at the DDL level, it will negatively impact the exception handler until that block of code is changed to reflect the new name. See #8 below for avoiding using a hardcoded name at all.

7. "Observation B: Only one violation out of potentially many can be reported by Oracle." I agree. In this case, that's acceptable. The user can change that one value and resubmit, and then the second error message will be displayed. This looping can occur until the user submits completely valid data, or gets frustrated and aborts.

8. "Observation C: Query the data dictionary at runtime to loop through the names of constraints that might be violated." Unfortunately, this approach fails to satisfy the primary goal of linking a specific constraint name with a specific error message/code. It's not enough to simply inform the user/support which constraint is being violated since the constraint name is likely to be user-unfriendly. It's *meta data* that we need at the client level rather than the raw object name - it's the mapping of constraint name to form field-specific message that is required.

9. "Observation D: Include submitted values in the error message." Yes, that is very helpful information, and we do provide that information to the user. The error message examples are just placeholders for this thread. ("Synchronization of labels visible in the GUI with the error messages presented to the user" is a complete topic unto itself.)

Thanks again for spending the time to help me work through this issue!

Kurt

Kurt,

1.
2.
3. <quote>Actually (according to this site), unique constraints can be enforced by non-unique indexes</quote>
You're quite right. Thank you.
4. I wouldn't code the pessimistic approach or implying one should.
5.
6. I was suggesting not to hardcode the constraint/index name in the INSTR function ... rather loop through the unique constraints for the given object(may have to go after unique indexes as well) ... that is, go through ALL_CONSTRAINTS, ALL_INDEXES and from there through ALL_CONS_COLUMNS and ALL_IND_COLUMNS to get the names of the columns. Of course, this code can get quite involved in order to become generic ... so yes, one should consider if the effort is really worth it. It is an “alternate approach” though … didn’t even consider the “elegant” part since … well, how is elegance being measured?
7.
8. Wasn't aware of that “primary goal” … or suggesting to stop at constraint/index names (ORA-00001 has that information already) ... but go from there to the actual column names (didn't realize PK and SSN are also used as GUI lables). But yes, if you want to report based on the GUI labels rather than the column names then, indeed, separate custom error messages are needed ... unless one has the mapping of GUI labels to column names in a database table and has decided to implement the above mentioned, quite heavy, dynamic method.
9.

Gabriel

TOM,

We have a DB where we store the user log-in ID which is unique. We have to develop something where if a login-id entered by a new user already exists then return 5 suggestive login ids which do not exist in our DB.

So how to go about it??

Thanks in anticipation..as always!

Tom,

I've this query written for the above requirement, where DUMMY is just a dummy table with 10 rows.

select *
from
(
select substr(substr(:p_user_id,1,instr(:p_user_id,'@',1)-1),1,special_char_pos)||
trunc(dbms_random.value('1','9999')) ||
substr(substr(:p_user_id,1,instr(:p_user_id,'@',1)-1),special_char_pos) Name_without_domain,
substr(:p_user_id,instr(:p_user_id,'@',1)) the_domain
from dummy,
(select case when
instr(substr(:p_user_id,1,instr(:p_user_id,'@',1)-1),'-',1) > 0
then
instr(substr(:p_user_id,1,instr(:p_user_id,'@',1)-1),'-',1)
when
instr(substr(:p_user_id,1,instr(:p_user_id,'@',1)-1),'_',1) > 0
then
instr(substr(:p_user_id,1,instr(:p_user_id,'@',1)-1),'_',1)
when
instr(substr(:p_user_id,1,instr(:p_user_id,'@',1)-1),'.',1) > 0
then
instr(substr(:p_user_id,1,instr(:p_user_id,'@',1)-1),'.',1)
else instr(:p_user_id,'@',1)
end special_char_pos
from dual
)
)
where not exists ( select null
from LOGIN
where USER_ID = Name_without_domain || the_domain
)
and rownum <= 5;


Please do let me know your views on this. Or if you could suggest something better (which I'm sure of)

Thanks as always.

Sorry Tom!

The query above is For suggesting 5 alternate Email IDs which are not present in the Database.

Regards,