This is a question onto defining foreign keys in a relational database.
Foreign keys are featured by the InnoDB engine and therefore all three
tables of the database use it:
1. `friends` main table
2. `relation` deploys a simple relation between rows of the main table,
'1-2' means '1 is friend of 2'
3. `category` table with two columns, integer primary key and referring
text explanation. It is for distributing persons of the `friends` table
into categories.
All three tables have an id column defined as smallint(5) unsigned NOT
NULL auto_increment as their first column. (that is trying to be used to
establish the FOREIGN KEY definitions)
Here is the output of the mysql client:
mysql> SHOW CREATE TABLE friends\G
*************************** 1. row ***************************
Table: friends
Create Table: CREATE TABLE `friends` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`firstname` varchar(22) collate latin1_german2_ci default NULL,
`lastname` varchar(30) collate latin1_german2_ci default NULL,
`nick` varchar(20) collate latin1_german2_ci default NULL,
`birthdate` date default NULL,
`gender` enum('m','f') collate latin1_german2_ci default 'm',
`category` smallint(5) unsigned default '0',
`phonehome` varchar(30) collate latin1_german2_ci default NULL,
`phonework` varchar(30) collate latin1_german2_ci default NULL,
`phonemobile` varchar(30) collate latin1_german2_ci default NULL,
`email` varchar(38) collate latin1_german2_ci default NULL,
`street` varchar(38) collate latin1_german2_ci default NULL,
`town` varchar(28) collate latin1_german2_ci default NULL,
`zip` smallint(5) unsigned default NULL,
`country` char(3) collate latin1_german2_ci default NULL,
`lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TI
MESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `Name` (`lastname`,`firstname`)
) *ENGINE=InnoDB* DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE relation\G
*************************** 1. row ***************************
Table: relation
Create Table: CREATE TABLE `relation` (
`id` smallint(5) unsigned NOT NULL,
`idx` smallint(5) unsigned NOT NULL,
KEY `idx` (`idx`),
KEY `id` (`id`),
CONSTRAINT `relation_ibfk_1` FOREIGN KEY (`idx`) REFERENCES `friends`
(`id`),
CONSTRAINT `relation_ibfk_2` FOREIGN KEY (`id`) REFERENCES `friends`
(`id`)
) *ENGINE=InnoDB* DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE category\G
*************************** 1. row ***************************
Table: category
Create Table: CREATE TABLE `category` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`name` char(40) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) *ENGINE=InnoDB* DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
---------------------------------------------------------------------------------------------------------------
Now the following FOREIGN KEY definitions work, defining FOREIGN KEY
relations from `relation` to `friends`:
ALTER TABLE `relation` ADD FOREIGN KEY (id) REFERENCES friends(id);
ALTER TABLE `relation` ADD FOREIGN KEY (idx) REFERENCES friends(id);
---------------------------------------------------------------------------------------------------------------
Only the one defining a FOREIGN KEY out of the main table `friends` onto
`categories` does not work:
mysql> ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES
category(id);
*ERROR 1452 (23000): Cannot add or update a child row*:
a foreign key constraint fails (`kontakte/#sql-73c_7`, CONSTRAINT
`#sql-73c_7_ibfk_1` FOREIGN KEY (`catego
ry`) REFERENCES `category` (`id`))
mysql>
Does anyone see why this does not work? (Other than repeating the error
message?)
How do I get it to work?
Regards,
Andy, Germany
