----- Original Message -----
From: "Ken Sommers" <[EMAIL PROTECTED]>
To: "pak" <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>
> To: "Ken Sommers" <[EMAIL PROTECTED]>
> 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" <[EMAIL PROTECTED]>
> > To: "pak" <[EMAIL PROTECTED]>
> > 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" <[EMAIL PROTECTED]>
> > | To: "Ken Sommers" <[EMAIL PROTECTED]>
> > | 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" <[EMAIL PROTECTED]>
> > | > To: "pak" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > | > 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" <[EMAIL PROTECTED]>
> > | > | To: "Ken Sommers" <[EMAIL PROTECTED]>;
> > <[EMAIL PROTECTED]>
> > | > | 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" <[EMAIL PROTECTED]>
> > | > | > To: "pak" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > | > | > 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" <[EMAIL PROTECTED]>
> > | > | > | To: <[EMAIL PROTECTED]>
> > | > | > | 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
> > <[EMAIL PROTECTED]>
> > | > | > | > To unsubscribe, e-mail
> > | > | > | <[EMAIL PROTECTED]>
> > | > | > | > Trouble unsubscribing? Try:
> > | > http://lists.mysql.com/php/unsubscribe.php
> > | > | > | >
> > | > | >
> > | > | >
> > | >
> >
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
