-- Thanks for the question regarding "updates with unqiue constraints", version 9.1.2

You Asked

A person can apply for any number of loans, sometimes the record of his/her previous loan application may be missing in the system. So after a new application is created, the technician might enter an old application from paper file. The loan table has a column that stores the sequence number of the application for that person that can be used for reporting. For example, how many second use loan applications have been entered. There is a unique constraint on the applicant_id and seq_nbr. We would like to update the sequence_nbr column everytime a new loan application is entered in the system to accurately depict the loan application number for the person.

For example,

Application date applicant_id sequence_number loan_id
01/01/1990 1 1 1
01/01/2000 1 2 2
01/01/1980 1 3 3


I would like to update the loan_id 3 record to sequence number 1 and loan_id 1 record to sequence_number 2 and loan_id 2 record to sequence number 3.
There is a unique constraint on applicant_id and sequence_number. I would like to do this when a new record is inserted.

and we said...

... sometimes the record of his/her previous loan application may be missing in the system....

HUH???? how could that possibly be even possible???


there is no such thing as a gap free sequence number in real life. Why do you need to have them be sequential starting at 1. You have application_date for ordering, you have count(*) for counting, that sequence number can and SHOULD be just a surrogate key and the key of that table is actually just sequence_number.


This is a data model done wrong.

If you need this sequential number for a given application, you would materialize it on RETRIEVAL:

select t.*, row_number() over (order by application_date) rn
  from t
 where t.applicant_id = :x
 order by application_date;

This field is derived from, dependent on the application date - therefore, we can synthesize it anytime you need it.


To answer a question like:

... For example, how many second use loan applications have been entered. ...

I presume you want to know how many people have two or more load applications - that is just

select count(*)
  from (select * from t group by applicant_id having count(*) > 2)
/

or

select * 
  from (select t.*, 
   row_number() over (partition by applicant_id order by application_date) rn,
   count(*) over (partition by applicant_id) cnt
        from t)
 where cnt >= 2
   and rn <= 2;

First query says "how many", second query says "show me the rows for the first two loan applications for each applicant with two or more"

Reviews

UK-update is easy   July 21, 2009 - 3pm Central time zone Bookmark | Bottom | Top Reviewer: Volker from Düsseldorf, GER Hi Tom, you are right, but we should help him/her with this problem without asking him/her to re-design the database. To original poster: UK-validation will be done on statement-level, not on row-level. So if you are able to update all loan-id's with one update (and after update UK is still valid) you should do it. Try this: SQL> create table a$tst 2 (id number not null 3 ,ori_id number not null 4 ,txt varchar2(20) not null) 5 / Table created. SQL> alter table a$tst 2 add constraint a$tst_uk 3 unique (id) 4 / Table altered. SQL> insert into a$tst values (1,1,'y') 2 / 1 row created. SQL> insert into a$tst values (2,2,'z') 2 / 1 row created. SQL> insert into a$tst values (3,3,'x') 2 / 1 row created. SQL> commit 2 / Commit complete. SQL> select a.*,ascii(a.txt) - 119 expected_value from a$tst a 2 / ID ORI_ID TXT EXPECTED_VALUE ---------- ---------- -------------------- -------------- 1 1 y 2 2 2 z 3 3 3 x 1 Let's say that EXPECTED_VALUE is what we want to see in ID. We can do it in one statement and see the result: SQL> update a$tst 2 set id = ascii(txt) - 119 -- the expected value 3 / 3 rows updated. SQL> commit 2 / Commit complete. SQL> select a.*,ascii(a.txt) - 119 expected_value from a$tst a 2 / ID ORI_ID TXT EXPECTED_VALUE ---------- ---------- -------------------- -------------- 2 1 y 2 3 2 z 3 1 3 x 1 That's all. And if you cannot do it in one statement or want do do it in a loop row-by-row, I can tell you that we (Oracle-people) have this big feature of DEFERRED CONSTRAINT. Having a deferrable UK and set this UK to deferred in a transaction, we may use several update-statement. UK-validation will only happen at commit-time. Followup   July 26, 2009 - 6am Central time zone: ... but we should help him/her with this problem without asking him/her to re-design the database. ... why???? why patch junk with junk? You are missing "lock table in exclusive mode" in your example, why do something that would lead to massive serialization? When an easier approach is available - GENERATE THE DATA AS YOU NEED WHEN YOU RETRIEVE. I did answer the question. Derived data need not be materialized until and unless and if you actually need it.

---

Write a Review