Re: [sqlite] Foreign key mismatch Error

[Madhan Kumar]

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  wrote:

> On Tue, 27 Sep 2011 14:58:06 +0400, Madhan Kumar
>  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

Re: [sqlite] Foreign key mismatch Error

[Kees Nuyt]

On Tue, 27 Sep 2011 14:58:06 +0400, Madhan Kumar
 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

[Simon Slavin]

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

[Paul Shaffer]

 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] (
"ItemID"integer 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] (
>>"ItemID"integer NOT NULL,
>>"AttributeID"integer 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] (
>>"ItemID"integer PRIMARY KEY AUTOINCREMENT NOT NULL,
>>"ItemName"varchar(255) NOT NULL COLLATE NOCASE DEFAULT ''
>> )
>>
>> CREATE TABLE [Attribute] (
>>"AttributeID"integer PRIMARY KEY AUTOINCREMENT NOT NULL,
>>"Name"varchar(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

Re: [sqlite] "foreign key mismatch" error

[Dan Kennedy]

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] (
>"ItemID"integer NOT NULL,
>"AttributeID"integer 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] (
>"ItemID"integer PRIMARY KEY AUTOINCREMENT NOT NULL,
>"ItemName"varchar(255) NOT NULL COLLATE NOCASE DEFAULT ''
> )
>
> CREATE TABLE [Attribute] (
>"AttributeID"integer PRIMARY KEY AUTOINCREMENT NOT NULL,
>"Name"varchar(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