Well, okay I just answered part of my own solution... SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE rep_table ADD FOREIGN KEY (`rep_company_code`) REFERENCES `company_table` (`company_code`) ON UPDATE CASCADE; SET FOREIGN_KEY_CHECKS=1; Allowed that to happen. I'm afraid to try the self referencing one b/c it seems there needs to be some logic in there to work. Perhaps that is something I will just have to manually UPDATE? > -----Original Message----- > From: Daevid Vincent [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 10, 2003 7:34 PM > To: [EMAIL PROTECTED] > Cc: 'Victoria Reznichenko' > Subject: RE: Foreign key update? > > > Thanks Victoria for the pointer. I should have looked there > first. Duh! > > Now for the help... I tried: > > ALTER TABLE rep_table ADD FOREIGN KEY (`rep_company_code`) REFERENCES > `company_table` (`company_code`) ON UPDATE CASCADE; > > But get "ERROR 1216: Cannot add a child row: a foreign key > constraint fails" > > I'm running version: 3.23.56-Max on RedHat 8 via RPM. > > I have data in both these tables, so dropping the tables and > redoing schema > is really not a graceful option if I can help it. > > Here are the two tables in their entirety. What have I done wrong? > > And just to clarify, I want this to work such that a change of the > company_table.company_code will trigger the same change to the > rep_table.rep_company_code. > > Ideally I'd also like it to be that if I change the > company_table.company_code, then any other company that has a > company_referal_code will change to the new company_code > too... Does that > make sense? Is that possible to have a self referencing > foreign key like > that? I thought I read that it isn't possible, but thought I'd ask. > > CREATE TABLE company_table ( > company_id mediumint(8) unsigned NOT NULL auto_increment, > company_timestamp timestamp(14) NOT NULL, > company_name varchar(255) NOT NULL default '', > company_code varchar(8) NOT NULL default '', > company_referal_code varchar(8) NOT NULL default '', > company_incept date NOT NULL default '0000-00-00', > company_phone varchar(20) NOT NULL default '', > company_fax varchar(20) NOT NULL default '', > company_url varchar(50) NOT NULL default '', > company_address1 varchar(70) NOT NULL default '', > company_address2 varchar(70) NOT NULL default '', > company_city varchar(50) NOT NULL default '', > company_state varchar(50) NOT NULL default '', > company_zip varchar(50) NOT NULL default '', > company_country varchar(50) NOT NULL default '', > company_type_table_id tinyint(3) unsigned default NULL, > company_type_level tinyint(3) unsigned default NULL, > company_registered enum('Y','N') default 'N', > company_notes longtext NOT NULL, > PRIMARY KEY (company_id), > KEY company_name (company_name), > KEY company_type_table_id (company_type_table_id), > KEY company_code (company_code) > ) TYPE=InnoDB; > > > CREATE TABLE rep_table ( > rep_id smallint(5) unsigned NOT NULL auto_increment, > rep_login varchar(15) NOT NULL default '', > rep_password varchar(15) NOT NULL default '', > rep_company_code varchar(8) NOT NULL default '', > rep_fname varchar(20) NOT NULL default '', > rep_lname varchar(20) NOT NULL default '', > rep_title varchar(50) NOT NULL default '', > rep_email varchar(50) NOT NULL default '', > rep_phone_office varchar(20) NOT NULL default '', > rep_phone_cell varchar(20) NOT NULL default '', > rep_address1 varchar(70) NOT NULL default '', > rep_address2 varchar(20) NOT NULL default '', > rep_city varchar(30) NOT NULL default '', > rep_state varchar(20) NOT NULL default '', > rep_zip varchar(20) NOT NULL default '', > rep_country varchar(30) NOT NULL default '', > rep_timestamp timestamp(14) NOT NULL, > rep_incept date NOT NULL default '0000-00-00', > rep_last_login date default '0000-00-00', > rep_admin enum('Y','N') NOT NULL default 'N', > rep_login_tally int(10) unsigned NOT NULL default '0', > rep_limit smallint(5) unsigned NOT NULL default '20', > rep_certified enum('Y','N') default 'N', > rep_notes longtext NOT NULL, > PRIMARY KEY (rep_id), > UNIQUE KEY rep_login (rep_login), > KEY rep_logpass (rep_login,rep_password), > KEY rep_company_code (rep_company_code) > ) TYPE=InnoDB; > > > -----Original Message----- > > From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, September 10, 2003 2:40 PM > > To: [EMAIL PROTECTED] > > Subject: Re: Foreign key update? > > > > Look at: > > http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
