Insert unique values problem.
andr...@yahoo.com
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.
Any helpful ideas?
Thanks!
Jonathan Leffler
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: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
bozon
insert into commontable(ordnum)
select
order_number
from
table1 t1
where
not exists(
select
*
from
commontable c
where
c.ordnum = t1.order_number
) ;
This should give you want you want.
OP/ED piece (without knowing anything else about the system and its
free so you know what it is worth ;-) )
What you should do his merge all of the order tables together with some
sort of translation so that all order numbers are unique:
select
order_number
from table1
union
select
order_number + 1000000
from table2
union
select
order_number + 2000000
from table3
...
Where the offset increases with each table and makes sure that the
records don't overlap. Then make this new table your uber-order table.
its order number should be a serial. Then create 10 views (todays
secret word) to back fit the rest of the system. You will now create
unique order numbers for your 10 different tables(views). Or if you are
using a version that has sequences then use one sequence to generate
the keys for all ten tables and then your update is very simple.
select * from table1 where ordnum > (select max(order_number) from
commontable);
select * from table2 where ordnum > (select max(order_number) from
commontable);
etc, etc ...