Re: [sqlite] Foreign key mismatch Error
Thank you all for your Reply.. I figured out the problem.. I added constraint for the Primary Key in Employee Table, then it got solved. I have missed to add constraint,, and it was the issue. On Wed, Sep 28, 2011 at 1:41 AM, Kees Nuyt k.n...@zonnet.nl wrote: On Tue, 27 Sep 2011 14:58:06 +0400, Madhan Kumar askmad...@gmail.com wrote: Hello.. In sqlite (version 3.7.7.1), I created two tables with foreign keys as shown below CREATE TABLE [*Employee*] ( [*EmpId*] INT NOT NULL, [EmpName] VARCHAR2(50)); CREATE TABLE [*Department*] ( [DeptID] INTEGER NOT NULL, [DeptName] VARCHAR2(20), [*EmpID*] INTEGER NOT NULL CONSTRAINT [FK_EMP_Dept] REFERENCES [Employee]([*EmpId*])); Using sqlite Expert Tool, I inserted a row in *Employee* Table - Success when i want to delete the same row from Employee table - gives error foreign key mismatch. When i try to insert a row in *Department* Table, gives error foreign key mismatch. I tried executing PRAGMA foreign_keys = ON and it returns 1 But still i am getting errors. Pls assist me to fix this issue. Thanks in advance. I think you have the reference the wrong way around. Employee should reference Department, the relation is n:1. The way you have it now, Department references employee, which means a department can only have one employee. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Foreign key mismatch Error
Hello.. In sqlite (version 3.7.7.1), I created two tables with foreign keys as shown below CREATE TABLE [*Employee*] ( [*EmpId*] INT NOT NULL, [EmpName] VARCHAR2(50)); CREATE TABLE [*Department*] ( [DeptID] INTEGER NOT NULL, [DeptName] VARCHAR2(20), [*EmpID*] INTEGER NOT NULL CONSTRAINT [FK_EMP_Dept] REFERENCES [Employee]([*EmpId*])); Using sqlite Expert Tool, I inserted a row in *Employee* Table - Success when i want to delete the same row from Employee table - gives error foreign key mismatch. When i try to insert a row in *Department* Table, gives error foreign key mismatch. I tried executing PRAGMA foreign_keys = ON and it returns 1 But still i am getting errors. Pls assist me to fix this issue. Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key mismatch Error
On 27 Sep 2011, at 11:58am, Madhan Kumar wrote: In sqlite (version 3.7.7.1), I created two tables with foreign keys as shown below CREATE TABLE [*Employee*] ( [*EmpId*] INT NOT NULL, [EmpName] VARCHAR2(50)); CREATE TABLE [*Department*] ( [DeptID] INTEGER NOT NULL, [DeptName] VARCHAR2(20), [*EmpID*] INTEGER NOT NULL CONSTRAINT [FK_EMP_Dept] REFERENCES [Employee]([*EmpId*])); Using sqlite Expert Tool, I inserted a row in *Employee* Table - Success when i want to delete the same row from Employee table - gives error foreign key mismatch. When i try to insert a row in *Department* Table, gives error foreign key mismatch. Can you please show us the 'INSERT' and 'DELETE FROM' statements you are using for the records which give this problem ? Ideally, starting from a blank database, INSERT a few records then try to DELETE one and show us the error you didn't expect to get. If you can use the sqlite3 shell tool to demonstrate the problem that would be even better than using the 'Expert Tool' you are talking about. But we'll try from the Expert Tool. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key mismatch Error
On Tue, 27 Sep 2011 14:58:06 +0400, Madhan Kumar askmad...@gmail.com wrote: Hello.. In sqlite (version 3.7.7.1), I created two tables with foreign keys as shown below CREATE TABLE [*Employee*] ( [*EmpId*] INT NOT NULL, [EmpName] VARCHAR2(50)); CREATE TABLE [*Department*] ( [DeptID] INTEGER NOT NULL, [DeptName] VARCHAR2(20), [*EmpID*] INTEGER NOT NULL CONSTRAINT [FK_EMP_Dept] REFERENCES [Employee]([*EmpId*])); Using sqlite Expert Tool, I inserted a row in *Employee* Table - Success when i want to delete the same row from Employee table - gives error foreign key mismatch. When i try to insert a row in *Department* Table, gives error foreign key mismatch. I tried executing PRAGMA foreign_keys = ON and it returns 1 But still i am getting errors. Pls assist me to fix this issue. Thanks in advance. I think you have the reference the wrong way around. Employee should reference Department, the relation is n:1. The way you have it now, Department references employee, which means a department can only have one employee. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] foreign key mismatch error
Original Message On Dec 4, 2009, at 12:31 PM, Paul Shaffer wrote: Below are 3 tables. When I delete a row in Item with related rows in Item_attribute I get the dreaded foreign key mismatch error. I've read through the documentation and don't see what I'm doing wrong. There are no FK mismatches in the three table definitions here. Maybe the problem is in a different part of the schema. Any other FK constraints reference table [Item]? Any ON DELETE triggers on table [Item]? There are no triggers in the database. Other tables have FK constraints to Item(ItemID), and I see what you mean. One of the other tables is probably causing the problem. I deleted the table below from the database, and cascading deletes are working without error. I don't see a problem with this table either. CREATE TABLE [T1] ( ItemIDinteger NOT NULL, AttributeID integer NOT NULL, UniqueID guid NOT NULL, PRIMARY KEY ([ItemID], [AttributeID]) , FOREIGN KEY ([AttributeID]) REFERENCES [T2]([AttributeID]) ON DELETE CASCADE, FOREIGN KEY ([ItemID]) REFERENCES [Item]([ItemID]) ON DELETE CASCADE ) Here is [T2], it's not the same as table as [Attribute] below. CREATE TABLE [T2] ( AttributeID integer PRIMARY KEY AUTOINCREMENT NOT NULL, Name nvarchar(255) NOT NULL COLLATE NOCASE DEFAULT '', ) sqlite v 1.6.20 CREATE TABLE [Item_attribute] ( ItemIDinteger NOT NULL, AttributeIDinteger NOT NULL, PRIMARY KEY ([ItemID], [AttributeID]) , FOREIGN KEY ([AttributeID]) REFERENCES [Attribute]([AttributeID]) ON DELETE CASCADE, FOREIGN KEY ([ItemID]) REFERENCES [Item]([ItemID]) ON DELETE CASCADE ) CREATE TABLE [Item] ( ItemIDinteger PRIMARY KEY AUTOINCREMENT NOT NULL, ItemNamevarchar(255) NOT NULL COLLATE NOCASE DEFAULT '' ) CREATE TABLE [Attribute] ( AttributeIDinteger PRIMARY KEY AUTOINCREMENT NOT NULL, Namevarchar(255) NOT NULL COLLATE NOCASE DEFAULT '' ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] foreign key mismatch error
Below are 3 tables. When I delete a row in Item with related rows in Item_attribute I get the dreaded foreign key mismatch error. I've read through the documentation and don't see what I'm doing wrong. sqlite v 1.6.20 CREATE TABLE [Item_attribute] ( ItemIDinteger NOT NULL, AttributeIDinteger NOT NULL, PRIMARY KEY ([ItemID], [AttributeID]) , FOREIGN KEY ([AttributeID]) REFERENCES [Attribute]([AttributeID]) ON DELETE CASCADE, FOREIGN KEY ([ItemID]) REFERENCES [Item]([ItemID]) ON DELETE CASCADE ) CREATE TABLE [Item] ( ItemIDinteger PRIMARY KEY AUTOINCREMENT NOT NULL, ItemNamevarchar(255) NOT NULL COLLATE NOCASE DEFAULT '' ) CREATE TABLE [Attribute] ( AttributeIDinteger PRIMARY KEY AUTOINCREMENT NOT NULL, Namevarchar(255) NOT NULL COLLATE NOCASE DEFAULT '' ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] foreign key mismatch error
On Dec 4, 2009, at 12:31 PM, Paul Shaffer wrote: Below are 3 tables. When I delete a row in Item with related rows in Item_attribute I get the dreaded foreign key mismatch error. I've read through the documentation and don't see what I'm doing wrong. There are no FK mismatches in the three table definitions here. Maybe the problem is in a different part of the schema. Any other FK constraints reference table [Item]? Any ON DELETE triggers on table [Item]? sqlite v 1.6.20 CREATE TABLE [Item_attribute] ( ItemIDinteger NOT NULL, AttributeIDinteger NOT NULL, PRIMARY KEY ([ItemID], [AttributeID]) , FOREIGN KEY ([AttributeID]) REFERENCES [Attribute]([AttributeID]) ON DELETE CASCADE, FOREIGN KEY ([ItemID]) REFERENCES [Item]([ItemID]) ON DELETE CASCADE ) CREATE TABLE [Item] ( ItemIDinteger PRIMARY KEY AUTOINCREMENT NOT NULL, ItemNamevarchar(255) NOT NULL COLLATE NOCASE DEFAULT '' ) CREATE TABLE [Attribute] ( AttributeIDinteger PRIMARY KEY AUTOINCREMENT NOT NULL, Namevarchar(255) NOT NULL COLLATE NOCASE DEFAULT '' ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
