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:29 PM Subject: Re: Referential Integrity > Ever play .,.Name that Code? > > I can delete those related rows in those 50 tables in about 5 lines.. > but first you gotta keep a table with the names of the 50 related tables and > the name of the foreign key in the row. > > then just loop around 50 times{ > get next table name and foreign key name form related_tables table > Delete from related_table Where foreign_key = primary_key; > } > > might work...! > > 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 > > | > | > | > > > | > | > > > | > | > > > | > > > >