thedude112286
asked on
Foreign Key troubles
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;
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;
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;
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.mysqlfreaks.com/errors/15.php