Solved

Foreign Key troubles

Posted on 2006-07-18
3
624 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 500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

728 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