List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:September 11 2003 2:42am
Subject:RE: Foreign key update?
View as plain text  
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:daevid@stripped] 
> Sent: Wednesday, September 10, 2003 7:34 PM
> To: mysql@stripped
> 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:victoria.reznichenko@stripped] 
> > Sent: Wednesday, September 10, 2003 2:40 PM
> > To: mysql@stripped
> > 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?> unsub=daevid@stripped
> 
> 

Thread
Foreign key update?Daevid Vincent10 Sep
  • Re: Foreign key update?Victoria Reznichenko10 Sep
    • RE: Foreign key update?Daevid Vincent11 Sep
      • RE: Foreign key update?Daevid Vincent11 Sep
      • Re: Foreign key update?Victoria Reznichenko11 Sep
        • RE: Foreign key update and Error :: 1217Daevid Vincent11 Sep
RE: Foreign key update?Dan Greene10 Sep
Re: Foreign key update and Error :: 1217Heikki Tuuri11 Sep
  • RE: Foreign key update and Error :: 1217 with v4.0.15Daevid Vincent12 Sep
    • Re: Foreign key update and Error :: 1217 with v4.0.15Heikki Tuuri12 Sep
      • RE: Foreign key update and Error :: 1217 with v4.0.15 [SOLVED]Daevid Vincent12 Sep