|
|
Home » U++ Library support » U++ SQL » SQLite On delete cascade .sch ?
SQLite On delete cascade .sch ? [message #51958] |
Wed, 26 June 2019 20:30 |
|
Xemuth
Messages: 387 Registered: August 2018 Location: France
|
Senior Member |
|
|
Hello community,
Is it possible to set "DELETE ON CASCADE" to a foreign key on .SCH ?
I've seen the " REFERENCES_CASCADE_(table,column) " but it don't work.
Thanks in advance.
Best Regards
[Updated on: Fri, 28 June 2019 22:16] Report message to a moderator
|
|
|
|
|
Re: SQLite On delete cascade .sch ? [message #51966 is a reply to message #51958] |
Thu, 27 June 2019 20:55 |
|
Xemuth
Messages: 387 Registered: August 2018 Location: France
|
Senior Member |
|
|
Well ,
According to stack overflow/Sqlite documentation, sqlite table cannot be altered after creation.
Since PGSQL alter table to add reference constraint, it do not work on sqlite.
Here is how we create a reference in SQLite :
CREATE TABLE "OW_EQUIPES_PLAYERS" (
"EP_EQUIPE_ID" integer,
"EP_PLAYER_ID" integer,
FOREIGN KEY("EP_EQUIPE_ID") REFERENCES "OW_EQUIPES"("EQUIPE_ID") ON DELETE CASCADE,
FOREIGN KEY("EP_PLAYER_ID") REFERENCES "OW_PLAYERS"("PLAYER_ID") ON DELETE CASCADE
);
So I had try something like that :
#ifndef REFERENCES
#define REFERENCES(x) ATTRIBUTE("FOREIGN KEY(@c) REFERENCES (@x) ON DELETE CASCADE,","");
#endif
#ifndef REFERENCES_CASCADE
#define REFERENCES_CASCADE(x) ATTRIBUTE("FOREIGN KEY(@c) REFERENCES (@x) ON DELETE CASCADE,","");
#endif
#ifndef REFERENCES_
#define REFERENCES_(n, x) ATTRIBUTE("FOREIGN KEY(@c) REFERENCES " #n "(@x) ON DELETE CASCADE,","");
#endif
#ifndef REFERENCES_CASCADE_
#define REFERENCES_CASCADE_(n, x) ATTRIBUTE("FOREIGN KEY(@c) REFERENCES " #n "(@x) ON DELETE CASCADE,","");
#endif
But Seems not working, moreover my bad knowledge of Upp SQL does not really help.
Have you any idea ?
Thanks in advance
Best Regard
|
|
|
|
Re: SQLite On delete cascade .sch ? [message #51969 is a reply to message #51958] |
Fri, 28 June 2019 12:23 |
|
Xemuth
Messages: 387 Registered: August 2018 Location: France
|
Senior Member |
|
|
Quote:Maybe try with INLINE_ATTRIBUTE ?
I had try but SQLite do not accept this kind of thing :
CREATE TABLE "OW_EQUIPES_PLAYERS" (
"EP_EQUIPE_ID" integer FOREIGN KEY("EP_EQUIPE_ID") REFERENCES "OW_EQUIPES"("EQUIPE_ID") ON DELETE CASCADE,
"EP_PLAYER_ID" integer FOREIGN KEY("EP_PLAYER_ID") REFERENCES "OW_PLAYERS"("PLAYER_ID") ON DELETE CASCADE,
);
Quote:Anyway, if it cannot be altered after creation, then there will be situation where doing it will be impossible, like two tables referencing each other - because one of them will not exist during creation.
It make sense for real SGBD but SQLite seems to don't care. I have been able to create my first table with this code :
CREATE TABLE "OW_EQUIPES_PLAYERS" (
"EP_EQUIPE_ID" integer,
"EP_PLAYER_ID" integer,
FOREIGN KEY("EP_EQUIPE_ID") REFERENCES "OW_EQUIPES"("EQUIPE_ID") ON DELETE CASCADE,
FOREIGN KEY("EP_PLAYER_ID") REFERENCES "OW_PLAYERS"("PLAYER_ID") ON DELETE CASCADE
);
Isn't there a keyword in .sch like INLINE ATTRIBUTE/ Attribute that force the add of instruction "foreign key(..."
at the end of table definition ?
Or a way to force "free text" in sch file ?
Best Regard
Xemuth
[Updated on: Fri, 28 June 2019 18:11] Report message to a moderator
|
|
|
|
|
Re: SQLite On delete cascade .sch ? [message #51977 is a reply to message #51958] |
Fri, 28 June 2019 21:23 |
|
Xemuth
Messages: 387 Registered: August 2018 Location: France
|
Senior Member |
|
|
Ok problem fixed,
SQlite3 can handle definition like that :
CREATE TABLE "OW_EQUIPES_PLAYERS" (
"EP_EQUIPE_ID" integer FOREIGN KEY("EP_EQUIPE_ID") REFERENCES "OW_EQUIPES"("EQUIPE_ID") ON DELETE CASCADE,
"EP_PLAYER_ID" integer FOREIGN KEY("EP_PLAYER_ID") REFERENCES "OW_PLAYERS"("PLAYER_ID") ON DELETE CASCADE,
);
But it need to be like that :
CREATE TABLE "OW_EQUIPES_PLAYERS" (
"EP_EQUIPE_ID" integer REFERENCES "OW_EQUIPES"("EQUIPE_ID") ON DELETE CASCADE,
"EP_PLAYER_ID" integer REFERENCES "OW_PLAYERS"("PLAYER_ID") ON DELETE CASCADE
);
Well it's logic.
Here is definition of foreign key on sqlite :
#ifndef REFERENCES_
#define REFERENCES_(n, x) INLINE_ATTRIBUTE("REFERENCES " #n "(" #x ")")
#endif
#ifndef REFERENCES_CASCADE_
#define REFERENCES_CASCADE_(n, x) INLINE_ATTRIBUTE("REFERENCES " #n "(" #x ") ON DELETE CASCADE")
#endif
Also, In SQLite, for each connection you do to sqlite, you must need to enable Foreign keys :
Sql sql;
sql.Execute("PRAGMA foreign_keys = ON;");
Personaly I did it at BDD loading :
if(sqlite3.Open("myDB.db")) {
SQL = sqlite3;
#ifdef _DEBUG
SqlSchema sch(SQLITE3);
All_Tables(sch);
if(sch.ScriptChanged(SqlSchema::UPGRADE)){
SqlPerformScript(sch.Upgrade());
}
if(sch.ScriptChanged(SqlSchema::ATTRIBUTES)){
SqlPerformScript(sch.Attributes());
}
if(sch.ScriptChanged(SqlSchema::CONFIG)) {
SqlPerformScript(sch.ConfigDrop());
SqlPerformScript(sch.Config());
}
sch.SaveNormal();
Sql sql;
sql.Execute("PRAGMA foreign_keys = ON;");
#endif
}
I guess, it is possible to enable it automaticly like lot of sqlite database viewer ?
[Updated on: Fri, 28 June 2019 22:15] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Tue May 28 16:09:52 CEST 2024
Total time taken to generate the page: 0.00685 seconds
|
|
|