andris_sh@yahoo.com wrote:
> Here's my situation.I have about 10 tables, which contain order
> numbers. Those numbers are not unique inside tables.
>
> My task is to populate and maintain separate table, let's call it
> <commontable>, which would contain UNIQUE order numbers from all
> tables. Here I've stopped, and I cannot get further.
>
> To get unique numbers from all tables just once is very simple,
> I write something like this:
>
> insert into commontable (ordnum)
> select order_number from table1
> union
> select order_number from table2
> union
> ...etc.
>
> All works nice. But my task is also to make incremental inserts in
> <commontable>, and here problems begins.
>
> My best try was something like this:
>
> insert into commontable (ordnum)
> select order_number
> from table1
> where not exists
> (select ordnum
> from commontable, table1
> where ordnum=order_number);
>
> Does not work. Subquery works only once and compares only first row of
> <commontable>. After that query just tries to insert all order_number
> values from table1, violates uniqueness, and that's all.
>
> I believe it is quite common task, but I haven't been able to find any
> example or idea, how to proceed with this.

I'm afraid the design of your database is back to front. You should
ensure the uniqueness of things that need to be unique up front (which
probably means maintaining commontable first and then having the other
tables refer to it). What constitutes the primary key of each of the 10
tables? Why didn't you use a consistent attribute name for the order
number? How often does a single order number appear in several
different tables - table2 and table7, say? Or is the duplication
limited to repetitions of the value in table1, but any number that
appears in table1 will never appear in table2 .. table 10? Are you sure
about that? Why are the 10 tables separate? What's common about them,
and what's different about them?

If commontable contains just the one attribute (column), what are you
using it for really?

From the limited information we have, it sounds as if you should have a
single table representing generic orders in some shape or form, and then
have 10 different order-type sub-tables - each of which could have a
simple referential constraint to the central order table (you're calling
it commontable). When an order is added, you create an entry in the
central table, and also populate the relevant other table or tables with
the other specialized information. There are still design issues
(disjointness, duplication in the sub-tables), but at least you'd have a
cohesive starting point.

I guess you're going to resist a redesign of the database.

Can you afford to use a UNION view? How often are you going to refer to
commontable (which would, perhaps, be better named commonview if you
adopt this idea)?

Have you considered INSERT triggers on the ten tables, each of which
does the relevant insert into commontable if the order number is not
already listed there?

The INSERT statement should read:

INSERT INTO CommonTable(OrdNum)
SELECT Order_Number
FROM Table1
WHERE Order_Number NOT IN (SELECT OrdNum FROM CommonTable);

This says "insert order numbers from table1 into common table where the
order number does not already appear in common table". The only
residual issue is can you select from CommonTable as well as insert into
it. If not, select into a temp table and then insert from the temp
table into CommonTable.

Given the likely rtealities of your situation, I think a one time load
followed by insert triggers is likely your best solution. However, you
should also develop a check script to ensure that you haven't
accidentally deleted something in common table that is actually still in
one of the other tables, or somehow evaded the insert trigger (unlikely).

What happens when a row is updated in table1 and the order number
changes? Can that happen? Do your permissions prevent it happening?
What happens when a row is deleted from table1 and it was the last row
that referenced a particular value in commontable? Do you have to check
whether the value still exists in any of the other tables? You need to
consider UPDATE and DELETE triggers, therefore, on table1 .. table10.

What triggers do you need on commontable? What could be the possible
referential constraint on that? I don't think there's a feasible
referential constraint that can be created declaratively - it would have
to work on the UNION of the other tables, and the performance doesn't
bear thinking about. So, you probably end up coding some sort of check
manually - and it won't be fast. That is, I'm afraid, the penalty for
the faulty database design.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/