List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:September 11 2003 2:34am
Subject:RE: Foreign key update?
View as plain text  
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

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