Sybase IQ’s referential integrity restricts concurrent updates or deletes on a primary table during loads or inserts on a foreign table.
First request |
Overlapping transaction’s request |
|---|---|
Request by one transaction for LOAD/INSERT/UPDATE/ ALTER TABLE ADD foreign key/ ALTER TABLE DROP foreign key to any foreign table |
to DELETE its associated primary table with deletable row(s). |
to UPDATE its associated primary table. |
|
to TRUNCATE its associated primary table. |
Sybase IQ also generates an error for a request by one transaction to ALTER TABLE ADD foreign key or DROP foreign key while there are old version(s) of foreign table and/or primary table in use by other transactions.
For both enforced and unenforced foreign key and primary key, ASIQ allows:
Simultaneous LOAD/INSERT on one or more foreign tables and the shared primary table.
Simultaneous LOAD/INSERT on foreign table(s) and DELETE/UPDATE/TRUNCATE TABLE on another one or more foreign table(s).
Simultaneous DELETE/UPDATE/TRUNCATE TABLE on 2 or more foreign tables, even if sharing the same primary table.
Simultaneous DELETE/TRUNCATE TABLE on foreign table(s) and DELETE/UPDATE/TRUNCATE TABLE on shared primary table.
ALTER TABLE ADD foreign key or DROP foreign key if no transaction is using any old version(s) of foreign/primary table and these unused old version(s) will be dropped as part of the ADD/DROP foreign key operation.
IQ’s table level versioning guarantees consistent referential integrity checks while allowing concurrent LOAD/INSERT/UPDATE operations on the foreign table and LOAD/INSERT operations on the primary table.
IQ also verifies that deleted old values do not exist in a foreign table when a transaction requesting DELETE or UPDATE starts. This provides consistent referential integrity checking during concurrent DELETE on a foreign table and DELETE/UPDATE on a PRIMARY Table.
To understand concurrent operations on foreign and primary tables, assume that there are two foreign key constraints among two foreign tables, ftab1 and ftab2, and one primary table, ptab. Assume that foreign key ftab1(fk1,fk2) references candidate key ptab(pk1,pk2). Foreign key ftab2(fk1,fk2) references the same candidate key. Candidate key ptab(pk1,pk2) can either be a primary key or a unique constraint.
Table 9-2 shows which operations on both foreign table and primary table should be allowed and which return an error. Table 9-2 applies only to enforced foreign keys and candidate key.
LOAD or INSERT ftab1 |
DELETE/ TRUNCATE TABLE ftab1 |
UPDATE ftab1 (fk1,fk2) |
Populate new index non-FK ftab1 (fk1,fk2) |
ADD FK ftab1 (fk1 fk2) |
DROP FK ftab1 (fk2, fk2) |
|
|---|---|---|---|---|---|---|
LOAD ftab2 |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
LOAD ptab |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
INSERT ftab2 |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
INSERT ptab |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
DELETE ftab2 TRUNCATE TABLE ftab2 |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
DELETE ptab TRUNCATE TABLE ptab |
Error |
Allowed |
Error |
Allowed |
Error |
Error |
UPDATE ftab2(fk1,fk2) |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
UPDATE ptab (pk1,pk2) |
Error |
Allowed |
Error |
Allowed |
Error |
Error |
Populate new index |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
Allowed |
QUERY (old version of ftab1/ptab in use with or without (fk1,fk2)) |
Allowed |
Allowed |
Allowed |
Allowed |
Error |
Error |
No old version of ftab2 in use |
Not Applicable |
Not Applicable |
Not Applicable |
Not Applicable |
Allowed (drop all unused old versions of ftab1) |
Allowed (drop all unused old versions of ftab1) |
Concurrency conflict occurs if one transaction loads foreign key columns while another updates associated candidate key columns. There is no concurrency conflict if one transaction loads foreign key columns while another updates non-associated candidate key columns on one of its associated candidate tables.
For efficient performance, a query on union all views
opens the tables referred to by those columns used as join keys
or group by columns. Until the transaction commits and the read
locks on the tables are released, you cannot alter or drop the tables
whose foreign keys are used as join conditions or grouping columns.
You can, however, load, insert, delete, and update these tables
while the query is running.
