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/[EMAIL PROTECTED]
