Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Foreign Key troubles

Posted on 2006-07-18
3
Medium Priority
?
628 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

597 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