----- Original Message -----
From: "Ken Sommers" <[EMAIL PROTECTED]>
To: "pak" <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>
> To: "Ken Sommers" <[EMAIL PROTECTED]>
> 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" <[EMAIL PROTECTED]>
> > To: "pak" <[EMAIL PROTECTED]>
> > 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" <[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]

Reply via email to