sqlite “On delete CASCADE” not working as expected in QT
I have a database in Qt.
it has four tables: maingroup, subgroup, parts, and position.this is my database:
CREATE TABLE `maingroup` (
`groupName`TEXT NOT NULL UNIQUE,
PRIMARY KEY(`groupName`)
);
CREATE TABLE `subgroup` (
`sub` TEXT NOT NULL UNIQUE,
`main` TEXT NOT NULL,
PRIMARY KEY(`sub`),
FOREIGN KEY(`main`) REFERENCES `maingroup`(`groupName`) ON DELETE CASCADE
);
CREATE TABLE `parts` (
`ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`Part_Number` TEXT,
`Type` TEXT NOT NULL,
`Value` TEXT,
`Voltage` TEXT,
`Quantity` TEXT,
`Position` TEXT,
`Picture` TEXT,
FOREIGN KEY(`Position`) REFERENCES `Position`(`Poistion`) ON DELETE CASCADE,
FOREIGN KEY(`Type`) REFERENCES `subgroup`(`sub`) ON DELETE CASCADE
);
Type in table parts is foreign key refers to column sub from table subgroup.
main in table subgroup is foreign key refers to column groupname in table maingroup.
my problem is when I try (delete from maingroup WHERE groupName= 'dd';) in DB Browser it deletes both parent and children.
But in QT this command(myQuery.exec("delete from maingroup WHERE groupName= 'dd'");) just deletes the parent field in maingroup table and not the child in subgroup and part table and the main column in subgroup table refers to a field in maingroup table that does not exist.
what is wrong here?what should i do?
c++ qt sqlite
asked Nov 25 '18 at 12:08
YousefYousef
185
add a comment |
I have a database in Qt.
it has four tables: maingroup, subgroup, parts, and position.this is my database:
CREATE TABLE `maingroup` (
`groupName`TEXT NOT NULL UNIQUE,
PRIMARY KEY(`groupName`)
);
CREATE TABLE `subgroup` (
`sub` TEXT NOT NULL UNIQUE,
`main` TEXT NOT NULL,
PRIMARY KEY(`sub`),
FOREIGN KEY(`main`) REFERENCES `maingroup`(`groupName`) ON DELETE CASCADE
);
CREATE TABLE `parts` (
`ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`Part_Number` TEXT,
`Type` TEXT NOT NULL,
`Value` TEXT,
`Voltage` TEXT,
`Quantity` TEXT,
`Position` TEXT,
`Picture` TEXT,
FOREIGN KEY(`Position`) REFERENCES `Position`(`Poistion`) ON DELETE CASCADE,
FOREIGN KEY(`Type`) REFERENCES `subgroup`(`sub`) ON DELETE CASCADE
);
Type in table parts is foreign key refers to column sub from table subgroup.
main in table subgroup is foreign key refers to column groupname in table maingroup.
my problem is when I try (delete from maingroup WHERE groupName= 'dd';) in DB Browser it deletes both parent and children.
But in QT this command(myQuery.exec("delete from maingroup WHERE groupName= 'dd'");) just deletes the parent field in maingroup table and not the child in subgroup and part table and the main column in subgroup table refers to a field in maingroup table that does not exist.
what is wrong here?what should i do?
c++ qt sqlite
asked Nov 25 '18 at 12:08
YousefYousef
185
add a comment |
I have a database in Qt.
it has four tables: maingroup, subgroup, parts, and position.this is my database:
CREATE TABLE `maingroup` (
`groupName`TEXT NOT NULL UNIQUE,
PRIMARY KEY(`groupName`)
);
CREATE TABLE `subgroup` (
`sub` TEXT NOT NULL UNIQUE,
`main` TEXT NOT NULL,
PRIMARY KEY(`sub`),
FOREIGN KEY(`main`) REFERENCES `maingroup`(`groupName`) ON DELETE CASCADE
);
CREATE TABLE `parts` (
`ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`Part_Number` TEXT,
`Type` TEXT NOT NULL,
`Value` TEXT,
`Voltage` TEXT,
`Quantity` TEXT,
`Position` TEXT,
`Picture` TEXT,
FOREIGN KEY(`Position`) REFERENCES `Position`(`Poistion`) ON DELETE CASCADE,
FOREIGN KEY(`Type`) REFERENCES `subgroup`(`sub`) ON DELETE CASCADE
);
Type in table parts is foreign key refers to column sub from table subgroup.
main in table subgroup is foreign key refers to column groupname in table maingroup.
my problem is when I try (delete from maingroup WHERE groupName= 'dd';) in DB Browser it deletes both parent and children.
But in QT this command(myQuery.exec("delete from maingroup WHERE groupName= 'dd'");) just deletes the parent field in maingroup table and not the child in subgroup and part table and the main column in subgroup table refers to a field in maingroup table that does not exist.
what is wrong here?what should i do?
c++ qt sqlite
asked Nov 25 '18 at 12:08
YousefYousef
185
I have a database in Qt.
it has four tables: maingroup, subgroup, parts, and position.this is my database:
CREATE TABLE `maingroup` (
`groupName`TEXT NOT NULL UNIQUE,
PRIMARY KEY(`groupName`)
);
CREATE TABLE `subgroup` (
`sub` TEXT NOT NULL UNIQUE,
`main` TEXT NOT NULL,
PRIMARY KEY(`sub`),
FOREIGN KEY(`main`) REFERENCES `maingroup`(`groupName`) ON DELETE CASCADE
);
CREATE TABLE `parts` (
`ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`Part_Number` TEXT,
`Type` TEXT NOT NULL,
`Value` TEXT,
`Voltage` TEXT,
`Quantity` TEXT,
`Position` TEXT,
`Picture` TEXT,
FOREIGN KEY(`Position`) REFERENCES `Position`(`Poistion`) ON DELETE CASCADE,
FOREIGN KEY(`Type`) REFERENCES `subgroup`(`sub`) ON DELETE CASCADE
);
Type in table parts is foreign key refers to column sub from table subgroup.
main in table subgroup is foreign key refers to column groupname in table maingroup.
my problem is when I try (delete from maingroup WHERE groupName= 'dd';) in DB Browser it deletes both parent and children.
But in QT this command(myQuery.exec("delete from maingroup WHERE groupName= 'dd'");) just deletes the parent field in maingroup table and not the child in subgroup and part table and the main column in subgroup table refers to a field in maingroup table that does not exist.
what is wrong here?what should i do?
c++ qt sqlite
c++ qt sqlite
asked Nov 25 '18 at 12:08
YousefYousef
185
asked Nov 25 '18 at 12:08
YousefYousef
185
edited Nov 25 '18 at 12:24
Yousef
asked Nov 25 '18 at 12:08
YousefYousef
185
asked Nov 25 '18 at 12:08
YousefYousef
185
asked Nov 25 '18 at 12:08
YousefYousef
185
185
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You need to turn on the foreign-key pragma by executing another statement before your DELETE statement.
QSqlQuery q;
q.exec("PRAGMA foreign_keys = ON");
q.exec("DELETE FROM ...");
This was able to cascade deletes, and should also be sufficient to solve other foreign-key related issues.
Credits to this forum.qt.io post.
answered Nov 25 '18 at 13:51
TrebuchetMSTrebuchetMS
2,4601822
thank you so much. I thought that its enough to enable it in db browser.
– Yousef
Nov 25 '18 at 18:54
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53467283%2fsqlite-on-delete-cascade-not-working-as-expected-in-qt%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need to turn on the foreign-key pragma by executing another statement before your DELETE statement.
QSqlQuery q;
q.exec("PRAGMA foreign_keys = ON");
q.exec("DELETE FROM ...");
This was able to cascade deletes, and should also be sufficient to solve other foreign-key related issues.
Credits to this forum.qt.io post.
answered Nov 25 '18 at 13:51
TrebuchetMSTrebuchetMS
2,4601822
thank you so much. I thought that its enough to enable it in db browser.
– Yousef
Nov 25 '18 at 18:54
add a comment |
You need to turn on the foreign-key pragma by executing another statement before your DELETE statement.
QSqlQuery q;
q.exec("PRAGMA foreign_keys = ON");
q.exec("DELETE FROM ...");
This was able to cascade deletes, and should also be sufficient to solve other foreign-key related issues.
Credits to this forum.qt.io post.
answered Nov 25 '18 at 13:51
TrebuchetMSTrebuchetMS
2,4601822
thank you so much. I thought that its enough to enable it in db browser.
– Yousef
Nov 25 '18 at 18:54
add a comment |
You need to turn on the foreign-key pragma by executing another statement before your DELETE statement.
QSqlQuery q;
q.exec("PRAGMA foreign_keys = ON");
q.exec("DELETE FROM ...");
This was able to cascade deletes, and should also be sufficient to solve other foreign-key related issues.
Credits to this forum.qt.io post.
answered Nov 25 '18 at 13:51
TrebuchetMSTrebuchetMS
2,4601822
You need to turn on the foreign-key pragma by executing another statement before your DELETE statement.
QSqlQuery q;
q.exec("PRAGMA foreign_keys = ON");
q.exec("DELETE FROM ...");
This was able to cascade deletes, and should also be sufficient to solve other foreign-key related issues.
Credits to this forum.qt.io post.
answered Nov 25 '18 at 13:51
TrebuchetMSTrebuchetMS
2,4601822
edited Dec 1 '18 at 7:10
answered Nov 25 '18 at 13:51
TrebuchetMSTrebuchetMS
2,4601822
answered Nov 25 '18 at 13:51
TrebuchetMSTrebuchetMS
2,4601822
answered Nov 25 '18 at 13:51
TrebuchetMSTrebuchetMS
2,4601822
2,4601822
thank you so much. I thought that its enough to enable it in db browser.
– Yousef
Nov 25 '18 at 18:54
add a comment |
thank you so much. I thought that its enough to enable it in db browser.
– Yousef
Nov 25 '18 at 18:54
thank you so much. I thought that its enough to enable it in db browser.
– Yousef
Nov 25 '18 at 18:54
thank you so much. I thought that its enough to enable it in db browser.
– Yousef
Nov 25 '18 at 18:54
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53467283%2fsqlite-on-delete-cascade-not-working-as-expected-in-qt%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
