Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
circular dependency in sqlite tables on insert [solved]
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Portage & Programming
View previous topic :: View next topic  
Author Message
DaggyStyle
Watchman
Watchman


Joined: 22 Mar 2006
Posts: 5128

PostPosted: Mon Jan 13, 2014 8:34 am    Post subject: circular dependency in sqlite tables on insert [solved] Reply with quote

Hello,

I have a qt based app that uses sqlite and have the following two tables:
Code:

create table Staff(dob integer,mob integer,yob integer,firstname varchar(30),id integer not null primary key,lastname varchar(30),staff_nationalities_id integer not null references Staff_nationalities(id),nickname varchar(30))

and
Code:
create table Staff_nationalities(staff_id integer not null references Staff(id),nation_id integer not null references Nation(id),main integer)

both tables have a fk to the other table.

when I try to insert data to them them I get this error:
Code:
insertion of row to table "Staff_nationalities" has failed: QSqlError(19, "Unable to fetch row", "foreign key constraint failed")


as I understand it, for adding table Staff I need Staff_nationalities to exist already and vice versa.
how can I fix this issue?

Thanks.
_________________
Only two things are infinite, the universe and human stupidity and I'm not sure about the former - Albert Einstein
ProjectFootball


Last edited by DaggyStyle on Tue Jan 14, 2014 6:33 am; edited 4 times in total
Back to top
View user's profile Send private message
aCOSwt
Moderator
Moderator


Joined: 19 Oct 2007
Posts: 2537
Location: Hilbert space

PostPosted: Mon Jan 13, 2014 8:47 am    Post subject: Reply with quote

Being said that I know absolutely nothing to sqlite in particular, and that I try to avoid circular references as much as possible and therefore would rethink the design, I can suggest you to consider that your foreign keys are most probably classified immediate (default) and therefore if adding a defferrable constraint would help.
_________________
Back to top
View user's profile Send private message
DaggyStyle
Watchman
Watchman


Joined: 22 Mar 2006
Posts: 5128

PostPosted: Mon Jan 13, 2014 9:18 am    Post subject: Reply with quote

aCOSwt wrote:
Being said that I know absolutely nothing to sqlite in particular, and that I try to avoid circular references as much as possible and therefore would rethink the design, I can suggest you to consider that your foreign keys are most probably classified immediate (default) and therefore if adding a defferrable constraint would help.


thanks for the tip, I'll read your links as I cannot see how I can separate the connection.

the issue is when inserting the rows looking in the net the issue can be either getting rid of the not null entry and update later or use invalid id and update later (beside your suggestion)
_________________
Only two things are infinite, the universe and human stupidity and I'm not sure about the former - Albert Einstein
ProjectFootball
Back to top
View user's profile Send private message
DaggyStyle
Watchman
Watchman


Joined: 22 Mar 2006
Posts: 5128

PostPosted: Mon Jan 13, 2014 9:55 am    Post subject: Reply with quote

from the link I understand that I can call
Code:
PRAGMA defer_foreign_keys = OFF
prior the row insertion, tried that but it still doesn't works, I think that for some reason defer_foreign_keys isn't supported on my system as calling
Code:
PRAGMA defer_foreign_keys
and printing the result returns no data while running
Code:
PRAGMA foreign_keys
does works and returns a valid output
_________________
Only two things are infinite, the universe and human stupidity and I'm not sure about the former - Albert Einstein
ProjectFootball
Back to top
View user's profile Send private message
aCOSwt
Moderator
Moderator


Joined: 19 Oct 2007
Posts: 2537
Location: Hilbert space

PostPosted: Mon Jan 13, 2014 10:12 am    Post subject: Reply with quote

DaggyStyle wrote:
from the link I understand that I can call
PRAGMA defer_foreign_keys = OFF

8O
There is something I do not understand in what you are trying to achieve.
Quote:
PRAGMA defer_foreign_keys = boolean;

When the defer_foreign_keys PRAGMA is ON, enforcement of all foreign key constraints is delayed until the outermost transaction is committed. The defer_foreign_keys pragma defaults to OFF so that foreign key constraints are only deferred if they are created as "DEFERRABLE INITIALLY DEFERRED".

_________________
Back to top
View user's profile Send private message
DaggyStyle
Watchman
Watchman


Joined: 22 Mar 2006
Posts: 5128

PostPosted: Mon Jan 13, 2014 10:27 am    Post subject: Reply with quote

aCOSwt wrote:
DaggyStyle wrote:
from the link I understand that I can call
PRAGMA defer_foreign_keys = OFF

8O
There is something I do not understand in what you are trying to achieve.
Quote:
PRAGMA defer_foreign_keys = boolean;

When the defer_foreign_keys PRAGMA is on, enforcement of all foreign key constraints is delayed until the outermost transaction is committed. The defer_foreign_keys pragma defaults to OFF so that foreign key constraints are only deferred if they are created as "DEFERRABLE INITIALLY DEFERRED".


unless I didn't understood the doc right (there is a good possibility for that), running PRAGMA defer_foreign_keys = OFF will enable me to insert rows even if foreign key is missing, I'm not sure that setting defferrable constraint to all foreign constantly just for the original load is the right way to fix it as I don't think I need it after initial load is done
_________________
Only two things are infinite, the universe and human stupidity and I'm not sure about the former - Albert Einstein
ProjectFootball
Back to top
View user's profile Send private message
aCOSwt
Moderator
Moderator


Joined: 19 Oct 2007
Posts: 2537
Location: Hilbert space

PostPosted: Mon Jan 13, 2014 10:51 am    Post subject: Reply with quote

My understanding is that :
- The defer_foreign_keys pragma defaults to OFF =>
When you [PRAGMA defer_foreign_keys = OFF] You do not change anything.
- You cannot proceed to your inserts because :
(Your foreign keys have not been created deferrable)&&(foreign_keys pragma is off).

=> (Do set defer_foreign_keys pragma to ON)||(create your foreign keys deferrable).
_________________
Back to top
View user's profile Send private message
DaggyStyle
Watchman
Watchman


Joined: 22 Mar 2006
Posts: 5128

PostPosted: Mon Jan 13, 2014 11:14 am    Post subject: Reply with quote

aCOSwt wrote:
My understanding is that :
- The defer_foreign_keys pragma defaults to OFF =>
When you [PRAGMA defer_foreign_keys = OFF] You do not change anything.
- You cannot proceed to your inserts because :
(Your foreign keys have not been created deferrable)&&(foreign_keys pragma is off).

=> (Do set defer_foreign_keys pragma to ON)||(create your foreign keys deferrable).


tried setting to on too but still it isn't working, see for example:
Code:

$ sqlite3
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> PRAGMA defer_foreign_keys;
sqlite> PRAGMA defer_foreign_keys = ON;
sqlite> PRAGMA defer_foreign_keys;

_________________
Only two things are infinite, the universe and human stupidity and I'm not sure about the former - Albert Einstein
ProjectFootball
Back to top
View user's profile Send private message
wjb
Guru
Guru


Joined: 10 Jul 2005
Posts: 381
Location: Fife, Scotland

PostPosted: Mon Jan 13, 2014 8:28 pm    Post subject: Reply with quote

My 2d's worth ...

Remove staff_nationalities_id and query it when you need it? It's potentially a list of values anyway so it'd never work as a field.

Set up triggers on Staff inserts/deletes to manage the associated Staff_nationalities record(s). (I was a bit surprised to see sqlte supports triggers)
Back to top
View user's profile Send private message
DaggyStyle
Watchman
Watchman


Joined: 22 Mar 2006
Posts: 5128

PostPosted: Mon Jan 13, 2014 8:48 pm    Post subject: Reply with quote

wjb wrote:
My 2d's worth ...

Remove staff_nationalities_id and query it when you need it? It's potentially a list of values anyway so it'd never work as a field.

Set up triggers on Staff inserts/deletes to manage the associated Staff_nationalities record(s). (I was a bit surprised to see sqlte supports triggers)


not sure why I need to remove it, unless I'm missing something if I remove it, it won't be in the db.

will look into triggers.
_________________
Only two things are infinite, the universe and human stupidity and I'm not sure about the former - Albert Einstein
ProjectFootball
Back to top
View user's profile Send private message
wjb
Guru
Guru


Joined: 10 Jul 2005
Posts: 381
Location: Fife, Scotland

PostPosted: Mon Jan 13, 2014 9:56 pm    Post subject: Reply with quote

I thought it's causing the cycle, and it's redundant because you can determine its value by doing a Select on staff_nationalities against a staff id.

Now I'm wondering what is the id field in staff_nationalities that staff.staff_nationalities_id references?
Back to top
View user's profile Send private message
DaggyStyle
Watchman
Watchman


Joined: 22 Mar 2006
Posts: 5128

PostPosted: Tue Jan 14, 2014 6:32 am    Post subject: Reply with quote

wjb wrote:
I thought it's causing the cycle, and it's redundant because you can determine its value by doing a Select on staff_nationalities against a staff id.

my issue isn't in the Select part, it is in the insert part.
wjb wrote:
Now I'm wondering what is the id field in staff_nationalities that staff.staff_nationalities_id references?

mmm, that is indeed a valid point, as one staff can have multiple entries in staff_nationalities where the link is staff_id, I see no logic in having staff.staff_nationalities_id in the first place.

that fixes the issue, thanks :)
_________________
Only two things are infinite, the universe and human stupidity and I'm not sure about the former - Albert Einstein
ProjectFootball
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Portage & Programming All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum