Fw: Referential Integrity

----- Original Message ----- From: "Ken Sommers" <mrsql.php@worldnet.att.net> To: "pak" <pak@transdata.com.au> Sent: Tuesday, July 10, 2001 10:46 PM Subject: Re: Referential Integrity > I started learning PHP Mysql about 2 weeks ago. > I'm still reading the tutorials. > Hopefuly some experienced programmers in PHP mysql will share how they > cascade their updates when they change a primary key. > > But my guess is If you keep a table of related table names /foreign key > names,,related to the table that needs to have it's updates cascaded,,you > can code it in a few lines; maybe 5 or 10 lines in PHP using mysql > functions. > I would love to see some real world examples. > > Ken > > ----- Original Message ----- > From: "pak" <pak@transdata.com.au> > To: "Ken Sommers" <mrsql.php@worldnet.att.net> > Sent: Tuesday, July 10, 2001 10:32 PM > Subject: Re: Referential Integrity > > > > Ken, > > What I am doing is programmatically to cascade update the detail/related > > tables, that is the way. > > Every body know Oracle handles great in database operation, that is > another > > story. > > I am asking for help, as I am not familiar with MySQL, is there other way > to > > do the update cascade besides doing so in application programming > > statements. > > ----- Original Message ----- > > From: "Ken Sommers" <mrsql.php@worldnet.att.net> > > To: "pak" <pak@transdata.com.au> > > Sent: Wednesday, July 11, 2001 3:09 PM > > Subject: Re: Referential Integrity > > > > > > | OK agreed, if the primary keys have any human value which might need > > | changing,,then i recommend Oracle ..:) > > | IF the Employee ID has the department embedded in it( bad design > > | imho),,,then I recommend a dbms with Ref Integrety built in. > > | But if you have a system with all primary keys as computer-generated > > | meaningless values, > > | I can't; see a reason to ever have to change one of those keys. > > | > > | Then what about deleting primary-keyed rows that point to other tables? > > | Maybe 50 other tables? hmmm it may be time for Oracle,,got any money? > > | > > | Ken > > | > > | > > | > > | ----- Original Message ----- > > | From: "pak" <pak@transdata.com.au> > > | To: "Ken Sommers" <mrsql.php@worldnet.att.net> > > | Sent: Tuesday, July 10, 2001 9:40 PM > > | Subject: Re: Referential Integrity > > | > > | > > | > Ken, > > | > These are trivial examples. What if the Administrator changes the > > | Employee's > > | > ID if the ID is not a pure numerical one and not a computer generated > ? > > | eg. > > | > IT001 represents employee in IT department. HR001 represents in Human > > | > Resources department. > > | > The point is the basic principle in database design : referential > > | integrity > > | > gaurantee the master-detail relationship be maintained without data > > loss. > > | > And this is the important function provided by DBMS. > > | > If we have a ER diagram when do the normalization, then we can see the > > | > tables invloved. How about if there are 200 tables in the database, > and > > 50 > > | > tables invloved, how many lines of code we have to write to do the > > update > > | in > > | > our program developed by ,say VB, VC++, Delphi. > > | > > > | > ----- Original Message ----- > > | > From: "Ken Sommers" <mrsql.php@worldnet.att.net> > > | > To: "pak" <pak@transdata.com.au> > > | > Sent: Wednesday, July 11, 2001 2:26 PM > > | > Subject: Re: Referential Integrity > > | > > > | > > > | > | Thanks for nice example, > > | > | Question,,Why would you ever have to change these codes > > | > | [EmpID or SuburbCode]?, if they are meaningless, arbitrary, probably > > | > | computer-generated numbers that should have no intrinsic or any > other > > | > | meaning of their own?,,Isn't; one meaningless number as good as the > > | next? > > | > | .. > > | > | ken > > | > | ----- Original Message ----- > > | > | From: "pak" <pak@transdata.com.au> > > | > | To: "Ken Sommers" <mrsql.php@worldnet.att.net> > > | > | Sent: Tuesday, July 10, 2001 9:08 PM > > | > | Subject: Re: Referential Integrity > > | > | > > | > | > > | > | > 1)Table Employee > > | > | > EmpID > > | > | > Name > > | > | > Address > > | > | > Phone > > | > | > SuburbCode (reference table Suburb) > > | > | > > > | > | > 2)Table Manager > > | > | > ManID (reference Employee) > > | > | > SubordinateID (reference table Employee) > > | > | > > > | > | > 3)Table Suburb > > | > | > SuburbCode > > | > | > Suburb > > | > | > State > > | > | > Postcode > > | > | > > > | > | > If the user/Administrator of the application/database change the > > | > | SuburbCode > > | > | > for a particular Suburb, > > | > | > he needs to change the table Employee also for every record with > > same > > | > | > SuburbCode. > > | > | > > > | > | > If the application allows the user/clerk changes the SuburbCode, > my > > | > | > application needs to change the detail > > | > | > table (Employee). > > | > | > > > | > | > This is the same case as if any changes of EmpID in table > Employee, > > | > | changes > > | > | > also need in table Manager explicitly. > > | > | > MySQL does not do that itself. > > | > | > > > | > | > > > | > | > ----- Original Message ----- > > | > | > From: "Ken Sommers" <mrsql.php@worldnet.att.net> > > | > | > To: "pak" <pak@transdata.com.au>; > > <mysql@lists.mysql.com> > > | > | > Sent: Wednesday, July 11, 2001 1:44 PM > > | > | > Subject: Re: Referential Integrity > > | > | > > > | > | > > > | > | > | Please give some examples where you would need the dbms to do > the > > | > | > | referential integrity? > > | > | > | Ken > > | > | > | ----- Original Message ----- > > | > | > | From: "pak" <pak@transdata.com.au> > > | > | > | To: "Ken Sommers" <mrsql.php@worldnet.att.net>; > > | > <mysql@lists.mysql.com> > > | > | > | Sent: Tuesday, July 10, 2001 8:31 PM > > | > | > | Subject: Re: Referential Integrity > > | > | > | > > | > | > | > > | > | > | > So is MySQL not suitable for large corporate database ? > > | > | > | > ----- Original Message ----- > > | > | > | > From: "Ken Sommers" <mrsql.php@worldnet.att.net> > > | > | > | > To: "pak" <pak@transdata.com.au>; > > <mysql@lists.mysql.com> > > | > | > | > Sent: Wednesday, July 11, 2001 1:24 PM > > | > | > | > Subject: Re: Referential Integrity > > | > | > | > > > | > | > | > > > | > | > | > | hello, > > | > | > | > | Do simple input validation. > > | > | > | > | > > | > | > | > | If the user is entering what is supposed to be a primary key > > | > value, > > | > | > | > | make sure it is a valid key before sticking it in any where, > > If > > | > it's > > | > | a > > | > | > | > bogus > > | > | > | > | key .tell the user to try again. > > | > | > | > | > > | > | > | > | If user wants you to delete rows from a primary table > > | > | (customer)that > > | > | > | have > > | > | > | > | "foreign keys"( cust ID in Orders)that are still pointing to > > | > | > | > | something.(related table)..tell the user that this customer > > | still > > | > | has > > | > | > | > | orders( yes you'll have to check yourself),,and deleting all > > | those > > | > | > | orders > > | > | > | > | would make the accountants and IRS really mad. and you can;t > > | > delete > > | > | > the > > | > | > | > | customer without deleting all the orders,,and tell them > > further > > | > more > > | > | > | > ,,that > > | > | > | > | deleting primary keys is bad practice anyway..should just > set > > | the > > | > | > active > > | > | > | > | flag to "NO"..cuz you still want all the history involved > with > > | > that > > | > | > | > customer > > | > | > | > | around.. and further more,..IF a few years down the line > that > > | > | customer > > | > | > | has > > | > | > | > | been inactive for a buncha years kill him or her then. and > all > > | the > > | > | > | related > > | > | > | > | orders .but only after the history files have been > summarized > > | and > > | > | > tucked > > | > | > | > | away. > > | > | > | > | > > | > | > | > | User wants to change a primary key value,,just don't do > it..:) > > | too > > | > | > much > > | > | > | > | work..OR tell 'em it will cost 'em. > > | > | > | > | > > | > | > | > | have fun, > > | > | > | > | Ken > > | > | > | > | > > | > | > | > | > > | > | > | > | > > | > | > | > | ----- Original Message ----- > > | > | > | > | From: "pak" <pak@transdata.com.au> > > | > | > | > | To: <mysql@lists.mysql.com> > > | > | > | > | Sent: Tuesday, July 10, 2001 7:17 PM > > | > | > | > | Subject: Referential Integrity > > | > | > | > | > > | > | > | > | > > | > | > | > | > MySQL does not support RI, anyone has good suggestion that > > do > > | > this > > | > | > in > > | > | > | > the > > | > | > | > | > program ? > > | > | > | > | > As this would be a nightmare if I have 50 detail tables to > > | > update > > | > | > | > | > programmatically. > > | > | > | > | > > > | > | > | > | > > > | > | > | > | > > | > | > > > | > --------------------------------------------------------------------- > > | > | > | > | > Before posting, please check: > > | > | > | > | > > > http://www.mysql.com/manual.php (the manual) > > | > | > | > | > http://lists.mysql.com/ > > (the list archive) > > | > | > | > | > > > | > | > | > | > To request this thread, e-mail > > | > <mysql-thread79070@lists.mysql.com> > > | > | > | > | > To unsubscribe, e-mail > > | > | > | > | > <mysql-unsubscribe-MrSql.Php=worldnet.att.net@lists.mysql.com> > > | > | > | > | > Trouble unsubscribing? Try: > > | > | > http://lists.mysql.com/php/unsubscribe.php > > | > | > | > | > > > | > | > | > > > | > | > | > > > | > | > > > | > > > >