MySQL - on update cascade (multiple tables) -


first - i've been looking answer on past few days no luck. meaning i've seen answers, tried them , still errors. i'm point looking @ code makes me sick. appreciated.

i have 3 tables clients, projects , project_notes. project can assigned 1 client, clients can have multiple projects. project can have multiple notes note can assigned 1 project.

what i'm looking if 'trash' client projects associated client 'trashed' well. project notes projects trashed 'trashed' well.

i can 'trash' project 'trash' associated project notes 'trashed' well.

i assume need use foreign keys , on update cascade - i've been trying. think i'm screwing way primary keys set - new me wrong.

i can create tables no problem. can insert data tables without , problem. however, 1 run update query on either clients or projects table i'm not longer able insert data table except clients.

here's code used create tables:

create table clients (   clientid int unsigned not null auto_increment,   companyname varchar(128),   clienttrash tinyint(1) unsigned not null default '0',   primary key (clientid, clienttrash),   index (companyname) )engine=innodb;   create table projects (   projectid int unsigned not null auto_increment,   clientid int unsigned not null,   projecttitle varchar(128),   projecttrash tinyint(1) unsigned not null default '0',   primary key (projectid, projecttrash),   index (projecttitle),   foreign key (clientid, projecttrash) references clients (clientid, clienttrash)     on delete cascade     on update cascade )engine=innodb;   create table project_notes (   projectnoteid int unsigned not null auto_increment,   projectid int unsigned not null,   note text,   projectnotetrash tinyint(1) unsigned not null default '0',   primary key (projectnoteid, projectnotetrash),   foreign key (projectid, projectnotetrash) references projects (projectid, projecttrash)     on delete cascade     on update cascade )engine=innodb;

(hopefully code formatted correctly - it's first time posting here).

if there's better (easier) method using on update cascade please let me know. , again thank , help.

forgot add: after running update query set clienttrash = 1 in clients table. i'll try insert values projects , end error #1452: cannot add or update child row: foreign key constraint fails (_clientmanage.projects, constraint projects_ibfk_1 foreign key (clientid, projecttrash) references clients (clientid, clienttrash) on delete cascade on update cascade)

i think confused use , applicability of referential integrity (foreign keys, cascade updates , cascade deletes). cascade updates , deletes way maintain referential integrity in related tables, changes on root/parent primary keys cascaded down dependent/child rows, relation not broken if parent id changes. way, referential integrity enforced when using innodb engine in mysql.

what want way enforce business rule, cascades state of entity down dependent entities. tool use @ database level trigger, special kind of stored routine executes whenever row inserted, updated, or deleted. can setup after update trigger on clients , projects tables cascade trash status.

from design , architecture point of view, kind of behavior typically handled on business logic code, not @ database level.


Comments

Post a Comment

Popular posts from this blog

c# - SharpSVN - How to get the previous revision? -

i trying find efficient way previous revision of file text comparison using sharpsvn. using (svnclient c = new svnclient()) { c.authentication.defaultcredentials = new networkcredential( configurationmanager.appsettings.get("svnserviceusername") , configurationmanager.appsettings.get("svnservicepassword") , configurationmanager.appsettings.get("svnservicedomain") ); c.authentication.sslservertrusthandlers += new eventhandler<svnsslservertrusteventargs>(authentication_sslservertrusthandlers); collection<svnfileversioneventargs> fileversioncollection = new collection<svnfileversioneventargs>(); svnrevisionrange range = new svnrevisionrange(0, this.hooks.revision); svnfileversionsargs args = new svnfileversionsargs(); args.retrieveproperties = true; args.range = range; foreach (svnchangeitem item in log.changedpaths) { string path = this.repositorypath + it...
Read more

c++ - Is it possible to compile a VST on linux? -

for class project i'm attempting write vst plugin backed cuda. current cuda workflow on linux box, i'd prefer compile , link there. according wikipedia , should possible (i couldn't find steinberg documentation relevant linux) can't find makefile or instructions on how build if aren't using xcode or visual studio. i'm vst 3 sdk doesn't support linux. when try compile plugin under linux, error: ./base/source/fatomic.cpp:39:30: fatal error: libkern/osatomic.h: no such file or directory this issue caused following code in "vst3 sdk/base/source/fatomic.cpp" #if mac #include <libkern/osatomic.h> #if mac_os_x_version_min_required > mac_os_x_version_10_4 #define native_atomic_type (volatile int32_t*) #else #define native_atomic_type (int32_t*) #endif #elif windows #include <windows.h> #endif but hope compiling under linux work vst sdk 2.4. reading. jvstwrapper seems run on lin...
Read more

url - Querystring manipulation of email Address in PHP -

i using third party application (email2http) send me emailid, body , message. unable retrieve email id due name appended on , enclosed < , >. here querystring getting. test.php?from=ajay+reddy &subject=cx&body=somebody when print variable in php returns name , not email id. $from = $_get['from']; echo $from; //output ajay reddy please suggest me how can emailid also. i dont want use post method due testing , debugging. it's working fine. forgot use htmlentities() in debugging.
Read more