Solved

Foreign Key troubles

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

777 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