?
Solved

Foreign Key troubles

Posted on 2006-07-18
3
Medium Priority
?
625 Views
Last Modified: 2008-01-09
When I run this script I get the following errors in MySql Query Browser:
Line 11: Can't create table '...users.frm' (errno: 150)                            ErrorNr.: 1005
Line 23: Can't create table '...searches.frm' (errno: 150)                            ErrorNr.: 1005

I've read about MySql's foreign key handling on the web and I can't figure out what my problem is.  Thanks very much for any help.

Here's the script:

CREATE DATABASE IF NOT EXISTS OSearch;

CREATE TABLE IF NOT EXISTS OSearch.Accounts (
 Id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 AcountName VARCHAR(25) NOT NULL,
 Password CHAR(28) NOT NULL,
 Name VARCHAR(100) NOT NULL,
 Email VARCHAR(100) NOT NULL
) TYPE = INNODB;

CREATE TABLE IF NOT EXISTS OSearch.Users (
 Id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 UserName VARCHAR(25) NOT NULL,
 Password CHAR(28) NOT NULL,
 AccountId INT NOT NULL,
 Name VARCHAR(100) NOT NULL,
 Email VARCHAR(100) NOT NULL,

 INDEX (AccountId),
 FOREIGN KEY (AccountId) REFERENCES Accounts(Id)
) TYPE = INNODB;

CREATE TABLE IF NOT EXISTS OSearch.Searches (
 Id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 UserId BIGINT UNSIGNED NOT NULL,
 Query VARCHAR(250) NOT NULL,
 Time DATE NOT NULL,

 INDEX (UserId),
 FOREIGN KEY (UserId) REFERENCES Users(Id)
) TYPE = INNODB;
0
Comment
Question by:thedude112286
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 8

Expert Comment

by:Autogard
ID: 17131159
This should help begin debugging:

http://www.mysqlfreaks.com/errors/15.php
0
 
LVL 13

Expert Comment

by:rhickmott
ID: 17131175
The Datatypes must be EXACTLY the same

Accounts has a BIGINT and Users has an INT and is UNSIGNED.

CREATE DATABASE IF NOT EXISTS OSearch;

CREATE TABLE IF NOT EXISTS OSearch.Accounts (
 Id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 AcountName VARCHAR(25) NOT NULL,
 Password CHAR(28) NOT NULL,
 Name VARCHAR(100) NOT NULL,
 Email VARCHAR(100) NOT NULL
) TYPE = INNODB;

CREATE TABLE IF NOT EXISTS OSearch.Users (
 Id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 UserName VARCHAR(25) NOT NULL,
 Password CHAR(28) NOT NULL,
 AccountId BIGINT UNSIGNED NOT NULL,
 Name VARCHAR(100) NOT NULL,
 Email VARCHAR(100) NOT NULL,

 INDEX (AccountId),
 FOREIGN KEY (AccountId) REFERENCES Accounts(Id)
) TYPE = INNODB;

CREATE TABLE IF NOT EXISTS OSearch.Searches (
 Id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 UserId BIGINT UNSIGNED NOT NULL,
 Query VARCHAR(250) NOT NULL,
 Time DATE NOT NULL,

 INDEX (UserId),
 FOREIGN KEY (UserId) REFERENCES Users(Id)
) TYPE = INNODB;

0
 
LVL 13

Accepted Solution

by:
rhickmott earned 2000 total points
ID: 17131215
The Above script I posted works on my local database running MySQL 5.21. The problem is

Your Trying to link the `Users` table to the `Accounts` table by joining `Users`.`AcountId` which is a SIGNED INT ( Meaning it can take values from -2147483648 to 2147483647) and joining it to `Accounts`.`Id` (which is a UNSIGNED BIGINT meaning it can take values from 0 to 18446744073709551615)

As a Result the query fails because the Data Types are not the same they must both take the same range of values ( Dont ask me why MySQL throws this cryptic error message it just does :( )







0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question