Re: Integrity - SQL vs Indexes vs Stored Procedures.

In article <2lPn4.274$Qn4.4161_at_cmnws01.we.mediaone.net>,   "Jon Griffin" <nospam_at_e88.org> wrote:
> I am interested in opinions from this group on the following issue:
> I am building a DB backend for some imported reports (coming from
 excel,
> word and other places). The backend is Oracle 8.1.5.
>
> Since these reports were designed by non-programming professionals
 there is
> no unique id. I am importing (from CSV) into a table and herein lies
 the
> catch. I need to make sure that the table doesn't get imported twice.
>
> I know that I can create an index of a bunch of columns and hope that
 they
> are unique (fairly easy actually), but theoretically indexes shouldn't
> really be used to enforce these rules. Anybody have a similar
 situation? I
> guess I could use a trigger and populate an extra field and then make
 that
> unique.
>
> I have a little time (unlike most projects lately) so I would rather
 do it
> right!
>
> TIA.
>

Since all you are doing is storing a report on-line I am not sure the unique key theory applies to your problem.

If you add a another column to hold a unique key, that will not prevent anyone from reloading the same report again. After all your trigger or Oracle sequence will generate a new unique key for the row so only an index on data could stop duplicates from being inserted, but this may not be possible for all reports.

After all, unless you strip the headings won't they be duplicated? Also you may well insert blank lines. You system could store these only once but you did not give much in the way of details.

Loading a report twice if you have a job control system should not be a problem. If you have no job control system or the customer will run the loads themselves then perhaps you can make use of the Oracle truncate command to clear the table out as part of the load logic.

If you do not truncate the table before reuse then you will need to add a line number type column to be able to guarentee that data is display in the right order since the last block deleted from will be first on the freespace list.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.