Skip to content
Snippets Groups Projects
  • Kirill Shcherbatov's avatar
    c8c73713
    sql: fix fkey exception for self-referenced table · c8c73713
    Kirill Shcherbatov authored
    UPDATE operation doesn't fail when fkey self-reference condition
    unsatisfied and table has other records.
    To do not raise error where it is not necessary Vdbe inspects
    parent table with OP_Found. This branch is not valid
    for self-referenced table since its looking for a tuple affected
    by UPDATE operation and since the foreign key has already
    detected a conflict it must be raised.
    
    Example:
    CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT, c INT, d TEXT, UNIQUE(a, b),
                    FOREIGN KEY(c, d) REFERENCES t6(a, b));
    INSERT INTO t6 VALUES(1, 'a', 1, 'a');
    INSERT INTO t6 VALUES(100, 'one', 100, 'one');
    UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;
    -- fk conflict must be raised here
    
    Needed for #3850
    Closes #3918
    c8c73713
    History
    sql: fix fkey exception for self-referenced table
    Kirill Shcherbatov authored
    UPDATE operation doesn't fail when fkey self-reference condition
    unsatisfied and table has other records.
    To do not raise error where it is not necessary Vdbe inspects
    parent table with OP_Found. This branch is not valid
    for self-referenced table since its looking for a tuple affected
    by UPDATE operation and since the foreign key has already
    detected a conflict it must be raised.
    
    Example:
    CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT, c INT, d TEXT, UNIQUE(a, b),
                    FOREIGN KEY(c, d) REFERENCES t6(a, b));
    INSERT INTO t6 VALUES(1, 'a', 1, 'a');
    INSERT INTO t6 VALUES(100, 'one', 100, 'one');
    UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;
    -- fk conflict must be raised here
    
    Needed for #3850
    Closes #3918