[SOLVED] Can't create table ... (errno: 150)

[Errant_Shadow]

Server version: 5.1.30

I've never got this error before, and my online searching has been more or less fruitless for help.

 

Here's the code generating this error:

CREATE TABLE subscriptions (
`id`		SMALLINT(5)	UNSIGNED	NOT NULL	AUTO_INCREMENT	COMMENT 'Primary Key. Used to link posts to subscribers and subscribers to accounts.', 
`account_id`	MEDIUMINT(	UNSIGNED	NOT NULL			COMMENT 'Foreign Key. Used to link dealers back to their accounts.',
`hits`		MEDIUMINT(	UNSIGNED	NOT NULL	DEFAULT '1'	COMMENT 'The number of hits received on the subscribers profile.',
`bio`		TEXT				NOT NULL			COMMENT 'Short description of business; under 3000 characters.',
`name`		VARCHAR(63)			NOT NULL			COMMENT 'The name of the dealership the user works for.', 
`phone`		VARCHAR(12)			NULL		DEFAULT NULL	COMMENT 'The phone number of the dealership the user works for.', 
`street`	VARCHAR(63)			NULL		DEFAULT NULL	COMMENT 'The street of the dealership the user works for.', 
`city`		VARCHAR(31)			NULL		DEFAULT NULL	COMMENT 'The city of the dealership the user works for.', 
`zip`		SMALLINT(5)	UNSIGNED	NULL		DEFAULT NULL	COMMENT 'The zip of the dealership the user works for.', 
`license_no`	VARCHAR(63)			NOT NULL			COMMENT 'The contractors license number.',
`rating`	TINYINT(1)	UNSIGNED	NOT NULL	DEFAULT '0'	COMMENT 'The users overall rating on a scale of 1 to 5.', 
`posts`		TINYINT(1)	UNSIGNED	NOT NULL	DEFAULT '3'	COMMENT 'The total number of advertisements alloted to this dealer this month.', 
`bill_date`	DATE				NOT NULL			COMMENT 'The next bill date; if overdue, subscription is frozen.',
`reg_date`	TIMESTAMP			NOT NULL	DEFAULT CURRENT_TIMESTAMP COMMENT 'The date and time of the users registration.',
PRIMARY KEY (id),
FOREIGN KEY (account_id) REFERENCES [other database].accounts(id)
) ENGINE = InnoDB;

 

... and here's the code for that table in the other database (foreign key):

CREATE TABLE accounts (
`id`		MEDIUMINT(	UNSIGNED	NOT NULL	AUTO_INCREMENT	COMMENT 'Primary Key. Used to link subscribers to accounts.',
`banned`	BOOLEAN				NOT NULL 	DEFAULT 0	COMMENT 'Records wether or not the account is banned; 0 = NO, !0 = YES.',
`gender`	ENUM('Mr.','Ms.')		NOT NULL			COMMENT 'The users title.',
`name`		VARCHAR(31)			NOT NULL			COMMENT 'The users first name.',
`email`		VARCHAR(63)			NOT NULL			COMMENT 'The users email address.',
`state`		VARCHAR(2)			NOT NULL			COMMENT 'The state of the dealership the user works for.',
`county`	VARCHAR(31)			NOT NULL			COMMENT 'The city of the dealership the user works for.', 
`PWORD`		VARCHAR(127)			NOT NULL			COMMENT 'The users password.',
`REMOTE_ADDR`	VARCHAR(15)			NOT NULL			COMMENT 'The users ip address.',
`reg_date`	TIMESTAMP			NOT NULL	DEFAULT CURRENT_TIMESTAMP COMMENT 'The date and time of the users registration.',
PRIMARY KEY (id), UNIQUE (email, REMOTE_ADDR)
) ENGINE = InnoDB;

[Mchl]

Try creating an index on account_id

[Errant_Shadow]

I'm not entirely sure how to do that and the MySQL manual mostly just confuses me...

 

Since the table is using the InnoDB engine, I see that I'm restricted to the BTREE index type but the syntax is still beyond me.

[Mchl]

Try like this

 

PRIMARY KEY (id),
FOREIGN KEY account_id (account_id) REFERENCES [other database].accounts(id)

[Errant_Shadow]

I tried...

 

KEY (account_id)

 

KEY account_id (account_id)

 

FOREIGN KEY account_id (account_id) REFERENCES [other database].accounts(id)

 

... and I tried removing the back quotes to see if that changed anything, but to no avail. It's still giving me the same error...

 

#1005 - Can't create table '[database].subscriptions' (errno: 150)

[Mchl]

Froma manual

 

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

 

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

 

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

 

Perhaps try SHOW ENGINE INNODB STATUS to see what details it gives

[Errant_Shadow]

#1227 - Access denied; you need the PROCESS privilege for this operation

 

I'm using phpMyAdmin to access these databases, but without that information, the only thing I can imagine might be the problem would be with the indexes. "...and it must have indexes on the referenced keys..." I'm not sure how to make -sure- these are set up right.

 

Also, what does "If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it" mean exactly? It seems simple when I read it, but perhaps I'm missing something.

 

 

[Mchl]

As I understand it, columns in both tables must be of EXACTLY same datatype (once I spend several minutes looking for an error, only to find out, that one of the columns was SIGNED and another UNSIGNED). This applies also to encodings and collations if columns are of string datatype.

 

Your table definitions seem to be fulfilling this requirement however.

[Errant_Shadow]

So then the only problem left is making indexes, but I've tried every which way that I know of to create an index on account_id, and the id field in the account table is the primary key; would it need to be indexed? (I thought that, bring the primary key, it already was)

[Mchl]

Yes, PRIMARY KAY is an index on its own.

 

One more thing to check: is there a matching id in 'accounts' table for EACH 'account_id' in 'subscriptions'?

[Errant_Shadow]

Hmm... good question. There is exactly 1 record in the accounts table, but since I'm creating the subscription table now, there are 0 records in it. Could this cause the problem?

 

Not every account will have a subscription, but every subscription will have an account. Will this work the way I'm designing it?

[Mchl]

Nah. That shouldn't be a problem.

 

I don't know what is.

[Errant_Shadow]

I mean, should I be making subscriptions.account_id reference account.id

 

(like this?)

 

... or should I make a separate foreign key for accounts.id that references subscriptions.account_id on each other database?

 

(like this?)

 

so, rather than using what I posted way back at the beginning, doing something like this (for the account table, rather than the subscription table):

 

CREATE TABLE `accounts` (
`id`		MEDIUMINT(	UNSIGNED	NOT NULL	AUTO_INCREMENT	COMMENT 'Primary Key. Used to link users to their PMs, posts and feedback.',
`banned`	BOOLEAN				NOT NULL 	DEFAULT 0	COMMENT 'Records wether or not the account is banned; 0 = NO, non 0 = YES.',
`gender`	ENUM('Mr.','Ms.')		NOT NULL			COMMENT 'The users title.',
`name`		VARCHAR(31)			NOT NULL			COMMENT 'The users first name.',
`email`		VARCHAR(63)			NOT NULL			COMMENT 'The users email address.',
`state`		VARCHAR(2)			NOT NULL			COMMENT 'The state of the dealership the user works for.',
`county`	VARCHAR(31)			NOT NULL			COMMENT 'The city of the dealership the user works for.', 
`PWORD`		VARCHAR(127)			NOT NULL			COMMENT 'The users password.',
`REMOTE_ADDR`	VARCHAR(15)			NOT NULL			COMMENT 'The users ip address.',
`reg_date`	TIMESTAMP			NOT NULL	DEFAULT CURRENT_TIMESTAMP COMMENT 'The date and time of the users registration.',
PRIMARY KEY (id),
FOREIGN KEY `db1_account_id` (`id`) REFERENCES `db1`.`subscriptions` (`account_id`) ON DELETE CASCADE,
FOREIGN KEY `db2_account_id` (`id`) REFERENCES `db2`.`subscriptions` (`account_id`) ON DELETE CASCADE,
FOREIGN KEY `db3_account_id` (`id`) REFERENCES `db3`.`subscriptions` (`account_id`) ON DELETE CASCADE,
UNIQUE (email, REMOTE_ADDR)
) ENGINE = InnoDB;

[Mchl]

First case.

[Mchl]

I think I got it

FOREIGN KEY account_id (account_id) REFERENCES otherDatabase.accounts(`id`)

[Errant_Shadow]

Unfortunately, same error =_=

[Mchl]

I don't know.

I have created these tables on my server.

[Errant_Shadow]

I've been able to create a foreign key when the tables are all on the same database, so this problem must have something to do with them being on separate databases...

[Mchl]

I did it on two databases.

 

Here's my SQL

 

USE test;
CREATE TABLE accounts (
`id`      MEDIUMINT(   UNSIGNED   NOT NULL   AUTO_INCREMENT   COMMENT 'Primary Key. Used to link subscribers to accounts.',
`banned`   BOOLEAN            NOT NULL    DEFAULT 0   COMMENT 'Records wether or not the account is banned; 0 = NO, !0 = YES.',
`gender`   ENUM('Mr.','Ms.')      NOT NULL         COMMENT 'The users title.',
`name`      VARCHAR(31)         NOT NULL         COMMENT 'The users first name.',
`email`      VARCHAR(63)         NOT NULL         COMMENT 'The users email address.',
`state`      VARCHAR(2)         NOT NULL         COMMENT 'The state of the dealership the user works for.',
`county`   VARCHAR(31)         NOT NULL         COMMENT 'The city of the dealership the user works for.',
`PWORD`      VARCHAR(127)         NOT NULL         COMMENT 'The users password.',
`REMOTE_ADDR`   VARCHAR(15)         NOT NULL         COMMENT 'The users ip address.',
`reg_date`   TIMESTAMP         NOT NULL   DEFAULT CURRENT_TIMESTAMP COMMENT 'The date and time of the users registration.',
PRIMARY KEY (id), UNIQUE (email, REMOTE_ADDR)
) ENGINE = InnoDB;
USE test1;
CREATE TABLE subscriptions (
`id`      SMALLINT(5)   UNSIGNED   NOT NULL   AUTO_INCREMENT   COMMENT 'Primary Key. Used to link posts to subscribers and subscribers to accounts.',
`account_id`   MEDIUMINT(   UNSIGNED   NOT NULL         COMMENT 'Foreign Key. Used to link dealers back to their accounts.',
`hits`      MEDIUMINT(   UNSIGNED   NOT NULL   DEFAULT '1'   COMMENT 'The number of hits received on the subscribers profile.',
`bio`      TEXT            NOT NULL         COMMENT 'Short description of business; under 3000 characters.',
`name`      VARCHAR(63)         NOT NULL         COMMENT 'The name of the dealership the user works for.',
`phone`      VARCHAR(12)         NULL      DEFAULT NULL   COMMENT 'The phone number of the dealership the user works for.',
`street`   VARCHAR(63)         NULL      DEFAULT NULL   COMMENT 'The street of the dealership the user works for.',
`city`      VARCHAR(31)         NULL      DEFAULT NULL   COMMENT 'The city of the dealership the user works for.',
`zip`      SMALLINT(5)   UNSIGNED   NULL      DEFAULT NULL   COMMENT 'The zip of the dealership the user works for.',
`license_no`   VARCHAR(63)         NOT NULL         COMMENT 'The contractors license number.',
`rating`   TINYINT(1)   UNSIGNED   NOT NULL   DEFAULT '0'   COMMENT 'The users overall rating on a scale of 1 to 5.',
`posts`      TINYINT(1)   UNSIGNED   NOT NULL   DEFAULT '3'   COMMENT 'The total number of advertisements alloted to this dealer this month.',
`bill_date`   DATE            NOT NULL         COMMENT 'The next bill date; if overdue, subscription is frozen.',
`reg_date`   TIMESTAMP         NOT NULL   DEFAULT CURRENT_TIMESTAMP COMMENT 'The date and time of the users registration.',
PRIMARY KEY (id),
FOREIGN KEY (account_id) REFERENCES test.accounts(`id`)
) ENGINE = InnoDB;

[Errant_Shadow]

Oh MySQL, you uppity whore =_=

 

Found the problem... I'm using phpMyAdmin to create all these.

 

It wouldn't create the database because I was sending the command from the database that the table is being CREATED in but it wanted me to send it from the database that the FOREIGN KEY is REFERENCING.

 

I got it to work:

CREATE TABLE `db2`.`subscriptions` (
`id`		SMALLINT(5)	UNSIGNED	NOT NULL	AUTO_INCREMENT	COMMENT 'Primary Key. Used to link users to their PMs, posts and feedback.', 
`account_id`	MEDIUMINT(	UNSIGNED	NOT NULL			COMMENT 'Foreign Key. Used to link dealers back to their accounts.',
...
PRIMARY KEY (`id`),
FOREIGN KEY `account_id` (`account_id`) REFERENCES `db1`.`accounts`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

 

but I had to execute that from db1, rather than db2 where it's being made