U++ forum: Welcome to the forum

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