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
Solved

Foreign Key troubles

Posted on 2006-07-18
3
621 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
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql between clause 2 32
MySQL Error Code 2 25
Creating Functions in phpMyAdmin 8 26
Help With Simple Database Design 7 50
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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