Unique Keys and rows returned/searched for

[JMac]

If you have an Unique Constraint on a key field, and you search for a row using the full and complete key value, does Oracle stop searching at the point it returns the first row or will it continue and try and find another row in order to raise a TOO_MANY_ROWS exception? (Which I believe is ANSI standard functionality ... isn't it????)

[chrisrlong]

Since a unique constraint is implemented through a unique index, and searching on that value will most likely use that index, it would both be unnecessary and impossible to either search for another record with that value or find one.

Having said that, if that index is not used, then whatever method is used (say, a Table Scan) will be done to completion. The TOO_MANY_ROWS has absolutely nothing to do with how the field is defined or constrained, but how the results of a query are used (in a sub-select returning values to a SELECT clause, an non-set predicate, or an INTO)

- Chris

[slimdave]

Since there is an exception to everything, let me point out that a deferrable (and maybe a non-validated?) unique constraint has a non-unique index.

I would guess (!) that although the execution plan shows that oracle is looking for a unique value, it would still detect the existence of multiple non-unique rows on a column or columns with a non-validated or deferred unique constraint.

Should be easy to put together a test case, but i can't be bothered.