|
|
|
| ||||||||||||||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
||||
|
||||
|
Errno 150 when adding foreign key (InnoDB)
Hi all
I have the following table: Code:
# CocoaMySQL dump # Version 0.5 # http://cocoamysql.sourceforge.net # # Host: localhost (MySQL 4.0.26-standard) # Database: psyguide_development # Generation Time: 2005-12-22 12:13:47 +0100 # ************************************************************ # Dump of table customers # ------------------------------------------------------------ CREATE TABLE `customers` ( `id` int(11) NOT NULL auto_increment, `name` varchar(100) default NULL, `plz` varchar(50) default NULL, `ort` varchar(50) default NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; # Dump of table orders # ------------------------------------------------------------ CREATE TABLE `orders` ( `id` int(11) NOT NULL auto_increment, `customer_id` int(11) default NULL, `bezeichnung` varchar(100) NOT NULL default '', `anzahl` int(11) NOT NULL default '1', PRIMARY KEY (`id`), KEY `customer_id_idx` (`customer_id`) ) TYPE=InnoDB; Now I'd like to create the following foreign key: Code:
alter table orders add constraint customer_id_fk foreign key(customer_id) references customer(id); But I get the following error: Quote:
What can I do? I don't have any data in the tables yet... Thanks for help. Josh
__________________
God, you gave man penis and brain, but only enough blood to run one at a time. ![]() my webpage: www.josh.ch my current project: phpmywebmin.josh.ch - a browser based file management application for webspace |
|
#2
|
||||
|
||||
|
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias) |
|
#3
|
||||
|
||||
|
Thanks a lot, man! :-)
So it seems I didn't realize that I have to write the constraint's name 2 times: alter table orders add constraint customer_id_fk foreign key customer_id_fk (customer_id) references customers(id); But can you tell me why? |
|
#4
|
||||
|
||||
|
Quote:
|
|
#5
|
||||
|
||||
|
Quote:
Hrm right, it also works this way with just 1 time writing name: alter table orders add constraint customer_id_fk foreign key (customer_id) references customers(id); |
|
#6
|
||||
|
||||
|
Damn! The same problem again and I don't get it!
Code:
drop database test;
create database test;
use test;
# CocoaMySQL dump
# Version 0.5
# http://cocoamysql.sourceforge.net
#
# Host: localhost (MySQL 4.0.26-standard)
# Database: psyguide_development
# Generation Time: 2005-12-22 22:01:35 +0100
# ************************************************************
# Dump of table _rails_standard
# ------------------------------------------------------------
CREATE TABLE `_rails_standard` (
`id` int(11) NOT NULL auto_increment,
`created_at` datetime NOT NULL default '0000-00-00 00:00:00',
`updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
`lock_version` int(11) NOT NULL default '1',
PRIMARY KEY (`id`)
) TYPE=InnoDB;
# Dump of table members
# ------------------------------------------------------------
CREATE TABLE `members` (
`id` int(11) NOT NULL auto_increment,
`created_at` datetime default '0000-00-00 00:00:00',
`updated_at` datetime default '0000-00-00 00:00:00',
`lock_version` int(11) NOT NULL default '1',
`nickname` varchar(40) default NULL,
`first_name` varchar(20) default NULL,
`last_name` varchar(20) default NULL,
`email` varchar(60) default NULL,
PRIMARY KEY (`id`)
) TYPE=InnoDB;
# Dump of table internet_radios
# ------------------------------------------------------------
CREATE TABLE `internet_radios` (
`id` int(11) NOT NULL auto_increment,
`created_at` datetime NOT NULL default '0000-00-00 00:00:00',
`updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
`lock_version` int(11) NOT NULL default '1',
`url` varchar(100) default NULL,
`playlist` varchar(100) default NULL,
`member_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=InnoDB;
ALTER TABLE internet_radios ADD CONSTRAINT member_id_fk FOREIGN KEY member_id_fk (member_id)
REFERENCES members (id)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
Still the same error "ERROR 1005: Can't create table './psyguide_development/#sql-251_10d.frm' (errno: 150)". Please give me a hint what's wrong again... Thanks. Last edited by j0sh : December 22nd, 2005 at 04:12 PM. |
|
#7
|
||||
|
||||
|
Oh damn why didn't anybody tell me that I have to have an index on the foreign key field?! Got a big book here but that's never explicitly mentioned! Could anybody please explain this to me?
Well, at least it works now.. ![]() |
|
#8
|
||||
|
||||
|
Quote:
|
|
#9
|
||||
|
||||
|
Thanks, but what do you mean with "recent versions"? I use MySQL 4.0.26-standard on Mac OS X. Should I upgrade to 4.x (I don't need 5.0 yet)?
|
|
#10
|
||||
|
||||
|
Take a look at changes between versions and use the latest productiton version.
|
|
#11
|
||||
|
||||
|
If you are going to upgrad you might as well upgrade to 5.x it is the production version since October of this year. As for outdated, MySQL comes out with updates at least monthly. Version 4.1 was the full production release in October of 2004, so yes, your version is considered outdated.
__________________
MySQL - reformat varchar dates to proper date type || MySQL - room vacancies || output results with column names with mysql only Latest row per group || Deleting duplicates || Gaps in auto increment column My site: sql help articles |
|
#12
|
||||
|
||||
|
Thanks. And how can I easily upgrade/update a version on Mac OS X?
|
![]() |
| Viewing: Dev Shed Forums > Databases > MySQL Help > Errno 150 when adding foreign key (InnoDB) |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|