[sqlite] Better example of fk mismatch problem

[Paul Shaffer]

I found where I was getting a 'foreign key mismatch' error, but I don't
know why. There are 2 versions of the last table below. They look like they
are about the same to me, but one causes the error. The error occurs when I
am doing a cascading delete in unrelated tables, and the problem table has
no rows. It's important for me since it impacts the code for a tool I am
writing. Thanks in advance for any help.


sqlite v 1.6.20

CREATE TABLE [Item] (
    [ItemID] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] varchar(255) NOT NULL COLLATE NOCASE DEFAULT 'undefined'
)


CREATE TABLE [Item_P] (
    [P_ID] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] varchar(255) NOT NULL COLLATE NOCASE DEFAULT ''
)


CREATE TABLE [P_column] (
    [ColumnID] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] varchar(255) NOT NULL COLLATE NOCASE DEFAULT '',
    [P_ID] integer NOT NULL,
    FOREIGN KEY ([P_ID])
        REFERENCES [Item_P]([P_ID]) ON DELETE CASCADE ON UPDATE CASCADE
)


CREATE TABLE [P_item] (
    [ItemID] integer NOT NULL,
    [ColumnID] integer NOT NULL,
    PRIMARY KEY ([ItemID], [ColumnID]),
    FOREIGN KEY ([ItemID]) REFERENCES [Item]([ItemID]) ON DELETE CASCADE ON
UPDATE CASCADE,
    FOREIGN KEY ([ColumnID]) REFERENCES [P_column]([ColumnID]) ON DELETE
CASCADE ON UPDATE CASCADE
)




-- no foreign key mismatch:

CREATE TABLE [P_Item_2] (
    [ItemID] integer NOT NULL,
    [ColumnID] integer NOT NULL,
    [ParentID] integer NOT NULL,
    PRIMARY KEY ([ItemID], [ColumnID]),
    FOREIGN KEY ([ColumnID] ,[ParentID])
        REFERENCES [P_item]([ColumnID],[ItemID]) ON DELETE CASCADE ON
UPDATE CASCADE
)


-- causes foreign key mismatch error:

CREATE TABLE [P_Item_2] (
    [ItemID] integer NOT NULL,
    [ColumnID] integer NOT NULL,
    [ParentID] integer NOT NULL,
    PRIMARY KEY ([ItemID], [ColumnID]),
    FOREIGN KEY([ColumnID])
        REFERENCES [P_item] ([ColumnID]) ON UPDATE CASCADE ON DELETE
    FOREIGN KEY([ParentID])
        REFERENCES [P_item] ([ItemID]) ON UPDATE CASCADE ON DELETE CASCADE,
CASCADE
)













_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users