MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMySQL Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old December 22nd, 2005, 06:17 AM
j0sh's Avatar
j0sh j0sh is offline
Ruby On Rails Junkie
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Switzerland
Posts: 486 j0sh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 20 h 18 m 57 sec
Reputation Power: 7
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:
ERROR 1005: Can't create table './psyguide_development/#sql-251_10d.frm' (errno: 150)


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

Reply With Quote
  #2  
Old December 22nd, 2005, 06:51 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 7th Plane (8000 - 8499 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,441 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 5 Days 22 h 55 m 41 sec
Reputation Power: 532
Make your customer id not null and then:
sql Code:
Original - sql Code
  1. ALTER TABLE `test`.`orders` ADD CONSTRAINT `FK_orders_2_1` FOREIGN KEY `FK_orders_2_1` (`customer_id`)
  2.     REFERENCES `customers` (`id`)
  3.     ON DELETE RESTRICT
  4.     ON UPDATE RESTRICT;

Reply With Quote
  #3  
Old December 22nd, 2005, 07:18 AM
j0sh's Avatar
j0sh j0sh is offline
Ruby On Rails Junkie
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Switzerland
Posts: 486 j0sh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 20 h 18 m 57 sec
Reputation Power: 7
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?

Reply With Quote
  #4  
Old December 22nd, 2005, 07:51 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 7th Plane (8000 - 8499 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,441 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 5 Days 22 h 55 m 41 sec
Reputation Power: 532
Quote:
Originally Posted by j0sh
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?
The important thing is to have customer_id as not null, the rest is just syntax

Reply With Quote
  #5  
Old December 22nd, 2005, 08:41 AM
j0sh's Avatar
j0sh j0sh is offline
Ruby On Rails Junkie
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Switzerland
Posts: 486 j0sh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 20 h 18 m 57 sec
Reputation Power: 7
Quote:
Originally Posted by pabloj
The important thing is to have customer_id as not null, the rest is just syntax


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);

Reply With Quote
  #6  
Old December 22nd, 2005, 03:50 PM
j0sh's Avatar
j0sh j0sh is offline
Ruby On Rails Junkie
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Switzerland
Posts: 486 j0sh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 20 h 18 m 57 sec
Reputation Power: 7
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.

Reply With Quote
  #7  
Old December 22nd, 2005, 04:28 PM
j0sh's Avatar
j0sh j0sh is offline
Ruby On Rails Junkie
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Switzerland
Posts: 486 j0sh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 20 h 18 m 57 sec
Reputation Power: 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..

Reply With Quote
  #8  
Old December 23rd, 2005, 03:33 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 7th Plane (8000 - 8499 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,441 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 5 Days 22 h 55 m 41 sec
Reputation Power: 532
Quote:
Originally Posted by j0sh
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..
Because recent versions of MySQL add it automatically and the need for it in previous versions is documented. Forget about big books and go for the official docs, free & effective

Reply With Quote
  #9  
Old December 23rd, 2005, 05:37 AM
j0sh's Avatar
j0sh j0sh is offline
Ruby On Rails Junkie
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Switzerland
Posts: 486 j0sh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 20 h 18 m 57 sec
Reputation Power: 7
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)?

Reply With Quote
  #10  
Old December 23rd, 2005, 06:29 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 7th Plane (8000 - 8499 posts)
 
Join Date: Jun 2001
Location: Outside US
Posts: 8,441 pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level)pabloj User rank is Colonel (50000 - 60000 Reputation Level) 
Time spent in forums: 3 Months 5 Days 22 h 55 m 41 sec
Reputation Power: 532
Take a look at changes between versions and use the latest productiton version.

Reply With Quote
  #11  
Old December 23rd, 2005, 08:24 AM
Guelphdad's Avatar
Guelphdad Guelphdad is offline
Hockey face
Dev Shed God 6th Plane (7500 - 7999 posts)
 
Join Date: Nov 2001
Location: St. Catharines, Canada
Posts: 7,605 Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level)Guelphdad User rank is General 1st Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Months 2 Weeks 4 Days 22 m 48 sec
Reputation Power: 1070
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.

Reply With Quote
  #12  
Old December 23rd, 2005, 08:53 AM
j0sh's Avatar
j0sh j0sh is offline
Ruby On Rails Junkie
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Switzerland
Posts: 486 j0sh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 20 h 18 m 57 sec
Reputation Power: 7
Thanks. And how can I easily upgrade/update a version on Mac OS X?

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMySQL Help > Errno 150 when adding foreign key (InnoDB)


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump




 Free IT White Papers!
 
How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

 
Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

 
Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

 
Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

 
Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 




© 2003-2010 by Developer Shed. All rights reserved. DS Cluster 11 Hosted by Hostway
For more Enterprise Application Development news, visit eWeek