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.
For both enforced and unenforced foreign key and primary key, SAP Sybase IQ allows:
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.