Delete from multiple tables in sql -


hello can me here.

i delete multiple tables.

i want delete id_grupo = 6 grupos. when delete delete gerir_posts id_grupo = 6. posts, id_post = 4, that's same id gerir_posts id_post; want delete too.

so realy want when remove grup , posts in grup deleted too.

picture tables

this example of db.

   create table utilizadores (     id_utilizador int auto_increment not null primary key,     nome varchar(50) );  insert utilizadores values (1,'admin');  create table grupos (     id_grupo int auto_increment not null primary key,     nome_grupo varchar(50) ); insert grupos values (1,'grupo');  create table gerir_grupos (     id_gerir_grupo int auto_increment not null primary key,     id_grupo int,     id_utilizador int,     foreign key (id_utilizador) references utilizadores(id_utilizador),     foreign key (id_grupo) references grupos(id_grupo) on delete cascade );  insert gerir_grupos values (1,1,1);  create table posts (     id_post int auto_increment not null primary key,     id_utilizador int,     titulo_do_post varchar(50),     corpo_do_post varchar(500),     foreign key (id_utilizador) references utilizadores (id_utilizador)  ); insert posts values (1,1,"teste","grupo teste");  create table gerir_posts (     id_gerir_post int auto_increment not null primary key,     id_post int,     id_grupo int,     foreign key (id_post) references posts (id_post) on delete cascade,     foreign key (id_grupo) references grupos (id_grupo)on delete cascade );  insert gerir_posts values (1,1,1);

if can help

picture tables

you cannot specify many tables in single delete statement.

use transaction

begin; delete posts post_id in (select post_id gerir_posts ...); delete gerir_posts ... ;     delete grupos ... ; commit;

this way, either deleted together, or stays intact.

use on delete cascade in constraints

create table posts (   post_id integer primary key,   ... );  create table gerir_posts (   ...   post_id integer;   constraint gerir_posts_fk      foreign key(post_id) references(posts.post_id)     on delete cascade, );

now if delete record posts, records in gerir_posts refer same post_id deleted.

you can use alter table add / modify constraints of existing tables.


Comments

Post a Comment

Popular posts from this blog

ios - Pass NSDictionary from Javascript to Objective-c in JavascriptCore -

i'm using javascriptcore in app. now, i'm passing variables jscontext can passed objective-c. however, 1 of variables, nsdictionary, not passing through correctly. run code below: var evaluate = function(variables) { app.setdictionary(variables.dictionary); } this simple example has these following methods set in jscontext . this setdictionary() method: - (void)setdicationary:(nsdictionary *)dictionary { self.mutabledictionary = [dictionary mutablecopy]; } this variables.dictionary : - (nsdictionary *)dictionary { return self.values; } and how call evaluate() : jsvalue *jsfunction = self.context[@"evaluate"]; jsvalue *value = [jsfunction callwitharguments:@[self.variables]]; however, in setdictionary method, don't nsdictionary, instead nsstring containing [object object] . any ideas how can solve this? although javascriptcore automatically converts types between objective-c or swift , javascript, suggest implement ex...
Read more

python - PyInstaller UAC not working in onefile mode -

hello everybody. i have small python project , want make single executable file . using... windows 7 python 3.4 pyinstaller 3.2.1 my pyinstaller command is, pyinstaller -y -w -f -n output_file_name --uac-admin --clean source_file.py this command works properly. single output file not ask admin rights when executed. , there's no shield mark on executable file icon. when remove -f option (equivalent --onefile), output executable file has shield mark on icon , ask me admin rights. not want. want single executable file. i found manifest file (output_file_name.exe.manifest) in dist\output_file_name folder. did... pyinstaller -y -w -f -n output_file_name --manifest output_file_name.exe.manifest --uac-admin --clean source_file.py but command doesn't work. single executable file still not ask admin rights. i have removed pyinstaller , installed recent development version. pip install git+https://github.com/pyinstaller/pyinstaller.git@develop but result...
Read more

php - Need to store a large amount of data in session with CI 3 but on storing large data in session it is itself destorying automatically -

i working on large web application need database retrieved session instead of database. first time retrieving db , saving session onward calls of data going session. it handling data @ stage when got more data stored in session session got destroyed automatically... "this problem seems similar question says title session destroyed after redirect other pages of site." i got answer questions point here how handle thing when know in future have more data stored inside session, i wanted know little bit more involved answer right in development stage on production when have user 100000 or more how session behave, application behave in same manner did before. kindly discuss me, researching, need corporation more technical in php discussion may solution many of people facing same kind of issues.. you need use native php session because php session stores id in cookie. codeigniter stores data in cookie , problem stuck. so either need use native php sessions...
Read more