Effect of the Unique_Scope Option on Updates
The UNIQUE_SCOPE option can affect the outcome of an update. For example, suppose you create an index on the employee numbers in an employee table, and the table contains employee numbers in sequence from 1 to 1000. If you issue an UPDATE statement that increments all employee numbers by 1, uniqueness is checked according to the UNIQUE_SCOPE option as follows:
• UNIQUE_SCOPE = ROW—Employee number 1 is incremented to 2. The row is checked for uniqueness-of course, employee number 2 already exists. Result: the update fails.
• UNIQUE_SCOPE = STATEMENT—Employees 1 through 1000 are incremented before uniqueness is checked. All employee numbers remain unique. Result: the update succeeds.
Last modified date: 04/26/2024