errno: 150 Cant create table in database

Question

When im trying to start script it fails on this table creation process(

INFO: AdKats role groups table not found. Attempting to add.
[14:14:59 11] [AdKats] CREATE TABLE `adkats_rolegroups` (
                      `role_id` int(11) unsigned NOT NULL,
                      `group_key` VARCHAR(100) NOT NULL,
                      PRIMARY KEY (`role_id`,`group_key`),
                      KEY `adkats_rolegroups_fk_role` (`role_id`),
                      KEY `adkats_rolegroups_fk_command` (`group_key`),
                      CONSTRAINT `adkats_rolegroups_fk_role` FOREIGN KEY (`role_id`) REFERENCES `adkats_roles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='AdKats - Connection of groups to roles'
[14:14:59 11] [AdKats] ERROR-7600: [Non-Query failed. [Adding AdKats role groups table]: MySql.Data.MySqlClient.MySqlException: Can't create table 'TonyS-BF3.adkats_rolegroups' (errno: 150)
   в PRoConEvents.AdKats.SafeExecuteNonQuery(MySqlCommand command)
   в PRoConEvents.AdKats.SendNonQuery(String desc, String nonQuery, Boolean verbose)]
[14:15:00 33] [AdKats] ALTER TABLE adkats_settings MODIFY setting_value varchar(10000)

I'd recommend checking out https://stackoverflow.com/a/4061333/6060606 as it's probably the foreign key constraint causing the issue. — Chris Satchell, Jan 18 '20 at 11:57

VBoka · Answer 1 · 2020-01-18T15:42:15.580

You are trying to create CONSTRAINT that references table adkats_roles column role_id.

When you create that table like for example:

create table adkats_roles (role_id int(11) unsigned NOT NULL,
                           INDEX name_index (role_id));

Then this is ok:

CREATE TABLE adkats_rolegroups(
role_id int(11) unsigned NOT NULL,
group_key VARCHAR(100) NOT NULL,
PRIMARY KEY (role_id,group_key),
KEY adkats_rolegroups_fk_role (role_id),
KEY adkats_rolegroups_fk_command (group_key),
CONSTRAINT adkats_rolegroups_fk_role FOREIGN KEY (role_id) 
REFERENCES adkats_roles (role_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 
COMMENT='AdKats - Connection of groups to roles'

Here is the DEMO

According to the code OP posted in one of his comments OP has a code where he creates this table. Here is the DEMO

score 0 · Answer 2 · answered Jan 18 '20 at 12:27

0

MySQL error 150 is not very descriptive. What it means is that the foreign key is not properly formed. Malformed foreign keys can have multiple causes:

The referenced table does not exist.
The types on the referenced and referred to columns are not the same.
The referenced column is not a key (preferably PRIMARY KEY) on the referring table.

My guess is that you simply have not created the table adkats_roles. You should declare role_id as the primary key in this table:

create table adketa_roles (
    role_id int auto_increment primary key,
    . . .
);

Then you should have no problem with the code.

I should note that MySQL -- unlike other databases -- allows foreign key references to columns where any index is created. This violates the spirit of foreign key references, which should be to a single row in the referenced table. I strongly encourage you to only have foreign key references to primary keys, and not to just any indexed column(s).

answered Jan 18 '20 at 12:27

Gordon Linoff

1,242,037
58
646
786

I have this code; (DROP TABLE IF EXISTS `adkats_roles`; CREATE TABLE IF NOT EXISTS `adkats_roles` ( `role_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `role_key` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `role_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`role_id`), UNIQUE KEY `role_key_UNIQUE` (`role_key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='AdKats - Role List';) – Tony Jan 18 '20 at 12:50
Hi @Tony, just to check, this code does go before the code : `CREATE TABLE adkats_rolegroups` ? Also, a feedback to my answer would be nice from you ? – VBoka Jan 18 '20 at 12:56
@Tony . . . That table definition looks correct (although it should be in the question not in a comment). It needs to go before any definition where a foreign key to the table is defined. – Gordon Linoff Jan 18 '20 at 13:43

errno: 150 Cant create table in database

2 Answers2