What is the easiest way to maintain two tables so that Table B has the same
records as Table B. Is there a way to do this with SQL.
I want to be able to update Table B so that new records are added or record
changes are reflected.
I assume there is TableA you omitted from your spec via a typo. I
assume you need to ensure that you cannot alter TableA without making
the same change to TableB.
I think the following *should* work...
Example tables:
CREATE TABLE TableA (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL
)
;
CREATE TABLE TableB (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL
)
;
A validation rule, in the form of a table-level CHECK constraint, to
ensure that TableA data matches TableB:
ALTER TABLE TableA ADD
CONSTRAINT TableA_sync_TableB
CHECK(1 = (
SELECT COUNT(*)
FROM TableB AS B1
WHERE B1.key_col = TableA.key_col
AND B1.data_col = TableA.data_col
))
;
Now an INSERT to TableA without the same to TableB is not possible
INSERT INTO TableA (key_col, data_col)
VALUES (1, 1)
;
because the CHECK bites.
We obviously need some means to INSERT the data, for which we can use a
VIEW (Query Object):
CREATE VIEW TableBA
AS
SELECT B1.key_col AS key_col_B,
A1.key_col AS key_col_A,
B1.data_col AS data_col_B,
A1.data_col AS data_col_A
FROM TableB AS B1
INNER JOIN TableA AS A1
ON B1.key_col = A1.key_col
WITH OWNERACCESS OPTION
;
(The WITH OWNERACCESS OPTION is included because you may want to remove
permissions from the tables).
Now this succeeds:
INSERT INTO TableBA (key_col_B, key_col_A, data_col_B, data_col_A)
VALUES (1, 1, 2, 2)
;
Obviously success depends on the first two arguments being equal and
the last two arguments being equal. To make things easier we can create
a helper proc (Parameter Query Object):
CREATE PROCEDURE AddData
(arg_key_col INTEGER, arg_data_col INTEGER)
AS
INSERT INTO TableBA (key_col_B, key_col_A, data_col_B, data_col_A)
VALUES (arg_key_col, arg_key_col, arg_data_col, arg_data_col)
WITH OWNERACCESS OPTION
;
Now this can be called as e.g.
EXECUTE AddData 3, 1;
Do the same for UPDATEs and DELETEs if required.
You may have spotted an issue, however.
Did you notice the VIEW definition used a left-to-right order TableB
then TableA e.g. in the INNER JOIN syntax? There is good reason for
this. Consider:
CREATE VIEW TableAB
AS
SELECT A1.key_col AS key_col_A,
B1.key_col AS key_col_B,
A1.data_col AS data_col_A,
B1.data_col AS data_col_B
FROM TableA AS A1
INNER JOIN TableB AS B1
ON A1.key_col = B1.key_col
WITH OWNERACCESS OPTION
;
Disregarding the left-to-right issue, views TableAB and TableBA are
*logically* equivalent. However, whereas the INSERT into TableBA
succeeds, the logically equivalent INSERT to TableAB fails e.g.
INSERT INTO TableAB (key_col_A, key_col_B, data_col_A, data_col_B)
VALUES (4, 4, 1, 1)
;
The CHECK bites. Why?
My understanding (gleaned from the ANSI SQL-92 standard) is that the
CHECK constraint test *should* be applied to the data after the SQL
statement has completed and that the INSERT should be single atomic
action with no implied order. The sequence of events should be:
1) Change data in the base tables according to the SQL statement.
2) Check that all table-level CHECK constraints (only on those tables
involved in the SQL statement) test true.
However, it is clear from the above that the INSERT is done on a table
by table basis, using a left-to-right order. So the actual sequence of
events is:
1) Change data in the first table (yes, I know, 'first' is supposed to
have no meaning in SQL!)
2) Check that all table-level CHECK constraints for the first table
test true.
3) Change data in the second table etc
In above example, the CHECK is tested before the data is added to
TableB, hence bites prematurely, we never get to step 3) and the whole
operation fails.
This is why I left TableB wide open e.g.
INSERT INTO TableB (key_col, data_col)
VALUES (-99, 0)
;
fails because there is no equivalent CHECK constraint ensuring TableB
cannot get out of sync with TableA. Well, you didn't make that clear
whether that is a requirement <g>.
Jamie.
--
