In this situation ALTER TABLE table ADD CASCADE will not work. When you try to change the PK value to the existing value, you violate the PK unique constraint.
CASCADE will work beautifully if you want to delete all for a given PK or update all the FKs as well as the PK to a different value. An example is where corruption has caused your PK values to jump from 55,555 to 999,999 or equivalent. "J. Stephen Wills" <[EMAIL PROTECTED]> wrote: >MessageIf these are actually defined as PK/FK columns, couldn't you "work 'em over" >w/some sort of CASCADE'g UPDATE operation? �I think this has to be part of the >definition of the PK/FK, but it's supposed to work. �Sorry I don't have detailed >info, just a conceptual suggestion f/a potential solution. > >HTH, >Steve > �----- Original Message ----- > �From: David Ebert > �To: [EMAIL PROTECTED] > �Sent: Tuesday, November 05, 2002 8:08 AM > �Subject: PK/FK cleanup > > > �I had some temporary data entry operators inputing to a new database with some >interesting results. �If a client is readmitted they would enter a new client record >and append either a '2' or a '?' to the last name, i.e., Dave Ebert, Dave Ebert2, >Dave Ebert? > > �The client record is the top level of a pyramid structure. �Below the client is the >admission record (many-to-one), and all records pertaining to the admission refer to >the admission record's PK. > > �I want to assign the admission record's PK to the FK records, but transfer the >admission record to the first client record. �No problem as I'm only talking about 60 >or so records. > > �However, the client ID is used as a reference in most of the detail tables >(assessments, services, outcomes, etc.) potentially 18 tables in all. �Reassigning >those client numbers involves finding out if a record by a given client number >exists, and if so to update it to the correct ID. �This may need to be done for up to >two spurious ID numbers. > > �I can to a series of updates to revise the ID number, but how would I do this aside >from picking through the candidate tables one at a time? > > �I'd appreciate any pointers. > > �tia > > �Dave Ebert > �Dave Ebert2 > �Dave Ebert? > �Huckleberry House > -- Albert Berry Full Time Consultant to PSD Solutions 350 West Hubbard, Suite 210 Chicago, IL 60610 312-828-9253 Ext. 32 __________________________________________________________________ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
