List:General Discussion« Previous MessageNext Message »
From:Andreas Krüger Date:March 12 2006 12:15pm
Subject:SQL Foreign Key
View as plain text  
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





Thread
SQL Foreign KeyAndreas Krüger12 Mar
  • Re: SQL Foreign KeyMartijn Tonies12 Mar