Mysql error no 105 , when creating a foriegn key constraint

Question

I have Two tables: sys_users and sys_roles.

After creating two tables I tried to create foreign keys with the following statement:

Alter table `hr_001`.`sys_users`
add constraint
`constraint_user_role` foreign key (`Role`) references
`hr_001`.`sys_roles`(`ID`) on delete Set null

But it gives me the following error

Query: Alter table hr_001.sys_users add constraint constraint_user_role foreign key (Role) references hr_001.sys_roles(ID...
Error Code: 1005
Can't create table 'hr_001.#sql-2e5c_1f' (errno: 150)

My table creation statements are

CREATE TABLE `sys_users` (
    `ID` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Username` VARCHAR (100) NOT NULL,
    `Password` VARCHAR (100) NOT NULL,
    `Active` TINYINT (1) NOT NULL DEFAULT '1',
    `Role` INT (11) NOT NULL,
    PRIMARY KEY (`ID`)
) ENGINE = INNODB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8;

CREATE TABLE `sys_roles` (
    `ID` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `Role` VARCHAR (50) NOT NULL,
    PRIMARY KEY (`ID`)
) ENGINE = INNODB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8;

Can anyone help on this?

http://stackoverflow.com/questions/825362/mysql-error-150-foreign-keys — , May 29 '16 at 09:20
but i think this is what you should look first http://stackoverflow.com/questions/1233032/mysql-error-150-cannot-create-table — , May 29 '16 at 09:21
@1000111 create statements CREATE TABLE `sys_roles` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Role` varchar(50) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 CREATE TABLE `sys_users` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Username` varchar(100) NOT NULL, `Password` varchar(100) NOT NULL, `Active` tinyint(1) NOT NULL DEFAULT '1', `Role` int(11) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 — Roshan, May 29 '16 at 09:26

score 1 · Answer 1 · edited May 23 '17 at 11:44

1

Try changing your foreign key creation syntax using not ON DELETE SET NULL but on ON DELETE CASCADE if your reference is created then take a look at this question and check your tables.

edited May 23 '17 at 11:44

Community

1
1

answered May 29 '16 at 09:24

score 0 · Answer 2 · answered May 29 '16 at 09:38

0

I'm sorry i have forgot to alter the sys_users table Role column as unsigned , becouse sys_roles id column is marked as unsigned, Thank you guys for fast replies.

answered May 29 '16 at 09:38

Roshan

3,236
10
41
63

Yes that's the reason. You may also look at this [**post**](http://stackoverflow.com/questions/9018584/error-code-1005-cant-create-table-errno-150) to gather some knowledge on some known cases of foreign key error – 1000111 May 29 '16 at 09:42

Mysql error no 105 , when creating a foriegn key constraint

2 Answers2