The referential integrity feature of SAP Sybase IQ restricts concurrent updates or deletes on a primary table during loads or inserts on a foreign table.
First request |
Request of Overlapping Transaction |
---|---|
Request by one transaction for LOAD/INSERT/UPDATE/ ALTER TABLE ADD foreign key/ ALTER TABLE DROP foreign key to any foreign table |
DELETE its associated primary table with deletable row(s). |
UPDATE its associated primary table. |
|
TRUNCATE its associated primary table. |
SAP Sybase IQ also generates an error for a request by one transaction to alter a table to add a foreign key or drop a foreign key while there are old versions of the foreign table, the primary table, or both, in use by other transactions.
Simultaneous load or insert on one or more foreign tables and the shared primary table.
Simultaneous load or insert on foreign tables and delete, or update, or truncate on another one or more foreign tables.
Simultaneous delete, or update, or truncate on two or more foreign tables, even if sharing the same primary table.
Simultaneous delete, or update, or truncate on foreign tables and delete, or update, or truncate on the 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.
The table-level versioning of SAP Sybase IQ guarantees consistent referential integrity checks while allowing concurrent load, or insert, or update operations on the foreign table and LOAD/INSERT operations on the primary table.
SAP Sybase IQ also verifies that there are no deleted old values in a foreign table when a transaction requesting DELETE or UPDATE starts. This provides consistent referential integrity checking during a concurrent delete operation on a foreign table and a delete or update operation on a PRIMARY Table.
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.
This table shows which operations on both tables (foreign and primary) should be allowed and which return an error. Information in the table 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 |
N/A |
N/A |
N/A |
N/A |
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 conflict if one transaction loads foreign key columns while another updates unassociated candidate key columns on one of its associated candidate tables.