ON UPDATE/DELETE CASCADE (SQLite)

['LinKin]

Hello,

I have this code which is NOT doing what I want..

When I change a team's name (via the script, not modifying it directly into the database with SQLite Browser), the registries at 'account_team' table linked to this team DON'T update their foreign key!

--------------------------------------------------------------------------------------------------------------------------------- 
--------------------                              Handled at < onResourceStart >                             -------------------- 
function initSQL() 
    ------------------------------------------------ 
    dbHandler = dbConnect("sqlite", "database.db") 
    ------------------------------------------------ 
     
     
        -- Creating 'teams' entity... 
    dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS `teams` (`name` TEXT PRIMARY KEY, `tag` TEXT UNIQUE, `color` TEXT UNIQUE, `points` INTEGER, `description` TEXT)") 
     
     
        -- Creating 'accounts' entity... 
    dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS `accounts` (`name` TEXT PRIMARY KEY, `last_nick` TEXT)") 
  
     
        -- Adding the missing column (because it references from 'teams' entity to 'accounts' entity ('accounts' is created AFTER 'teams)) 
    dbExec(dbHandler, "ALTER TABLE `teams` ADD COLUMN `owner` TEXT REFERENCES accounts(name) ON UPDATE CASCADE ON DELETE CASCADE") 
    dbExec(dbHandler, "CREATE UNIQUE INDEX IF NOT EXISTS unique_index ON teams(owner)") 
     
     
     
     
     
        -- Creating 'leader_team' entity... 
    dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS `leader_team` (`account` TEXT PRIMARY KEY, `team` TEXT, FOREIGN KEY (account) REFERENCES accounts(name) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (team) REFERENCES teams(name) ON UPDATE CASCADE ON DELETE CASCADE)") 
     
     
        -- Creating 'account_team' entity... 
    dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS `account_team` (`account` TEXT, `team` TEXT, PRIMARY KEY (account, team), FOREIGN KEY (account) REFERENCES accounts(name) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (team) REFERENCES teams(name) ON UPDATE CASCADE ON DELETE CASCADE)") 
     
     
        -- Creating 'join_requests' entity... 
    dbExec(dbHandler, "CREATE TABLE IF NOT EXISTS `join_requests` (`account` TEXT PRIMARY KEY, `team` TEXT, FOREIGN KEY (account) REFERENCES accounts(name) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (team) REFERENCES teams(name) ON UPDATE CASCADE ON DELETE CASCADE)") 
end 
--------------------------------------------------------------------------------------------------------------------------------- 

Example:

Data before the UPDATE:

TEAMS TABLE:
name	tag	color	points	description	owner
Street Warriors	|SW|	#00FFFF	0	Blablah	LinKin

ACCOUNT_TEAM TABLE:
account	team
LinKin	Street Warriors

Data after the UPDATE:

TEAMS TABLE:
name	tag	color	points	description	owner
Street Warr	|SW|	#00FFFF	0	Blablah	LinKin

ACCOUNT_TEAM TABLE:
account	team
LinKin	Street Warriors

As you see, the table ACCOUNT_TEAM didn't update its foreing key for the 'team' column.. Breaking the database integrity.

[Mr_Moose]

This kind of issues are usually caused by spelling/syntax errors, your code looks just fine and seems to be according to the syntax from what I can see, what if you run those lines directly to the SQL browser? does that cause any SQL errors for you? Maybe this could give you some help: https://www.sqlite.org/foreignkeys.html.

[tosfera]

And why aren't you using ID's which would be auto increment? Those shouldn't change whenever you edit your data making these things really... impossible to occur. I've always had trouble with cascades and therefore wrote then scriptwise myself. Too bad that MTA takes a shitload of memory to do it scriptwise...

['LinKin]

So I will have to UPDATE the FK's each time I change the team's name via script.

[Saml1er]

Nope. Just use ids as suggested by trosfera. That's what I did. Try it.