MySQL ERROR: 1005 Can't create table (errno : 150)

Question

when i want to change constraint of "matkul_prasyarat" table suddenly i got error and mysql delete "matkul_prasyarat" table and then when i trying to create it again it got error no 150 this is the error

there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT `matkul_prasyarat_ibfk_2` FOREIGN KEY (`nama_matkul`) REFERENCES `matkul_list` (`nama_matkul`) ON DELETE CASCADE ON UPDATE CASCADE

how to solve this problem ?

i want to make table "matkul_prasyarat"

PK no tinyint 20 AI 
   no_id_matkul int 20
   nama_matkul VARCHAR 50

this is the syntax that i can think of from MySQL: Can't create table (errno: 150) but it's error

CREATE TABLE matkul_prasyarat (
    no INT,
    nama_matkul VARCHAR,
    INDEX matkul_prasyarat_ibfk_2 (nama_matkul),
    FOREIGN KEY (nama_matkul)
        REFERENCES matkul_list(nama_matkul)
        ON DELETE CASCADE
) ENGINE=INNODB;

EDIT : syntax above alrd works i forgot to input size , thanks for ur help

Does this answer your question? [MySQL: Can't create table (errno: 150)](https://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150) — code builders, Dec 17 '19 at 21:30
yeah it's same, but i am new in mysql i dont know the sql syntax to solve that — Togan J.R, Dec 17 '19 at 21:34
It's usually better for the foreign key to reference the primary key, not another column. — Barmar, Dec 17 '19 at 21:51
@Barmar i've alrd dd index into nama_matkul, but it still error, iam trying to make sql syntax from the first comment but its error, would u mind to check my sintax ? ill edit this post — Togan J.R, Dec 17 '19 at 21:56

score 0 · Answer 1 · answered Dec 17 '19 at 21:37

0

Most likely cause of the error is a datatype mismatch.

Check the datatype of the referencing column (nama_matkul) in matkul_prasyarat

and compare it to the datatype referenced column (nama_matkul) in matkul_list table.

Verify the matkul_list table exists, and that it has an index with nama_matkul as the leading (first) column.

We can check the datatype of the column, and the existence of a suitable index, by examining the output from:

 SHOW CREATE TABLE matkul_list

The datatype of the nama_matkul column has to exactly match the datatype of the referencing column.

If neither of those is the issue, make sure matkul_list table is using InnoDB storage engine.

answered Dec 17 '19 at 21:37

spencer7593

106,611
15
112
140

why i can only get these CREATE TABLE `matkul_list` ( `no` int(11) NOT NU... how to get full result? – Togan J.R Dec 17 '19 at 21:42
The error message specifically says that the problem is that there's no index on the column referenced in the FK. Why do you think that means it's a datatype mismatch? – Barmar Dec 17 '19 at 21:43
1

@Barmar, the error message goes on: *"...or the data types in the table do not match the ones in the referenced table..."* – Bill Karwin Dec 17 '19 at 21:52
@Barmar: There are several reasons that InnoDB storage engine will report error 150 back to MySQL. In my experience, the most common cause of the error is a datatype mismatch (e.g. BIGINT vs INT UNSIGNED, max length or characterset for VARCHAR, et al.). My answer also indicated other common causes: missing index, referenced table is not InnoDB storage engine. I didn't intend to make an exact diagnosis (insufficient information), nor did I intend to be comprehensive. I prescribed a short list of things we check first. But I would be somewhat surprised if the problem wasn't on that short list. – spencer7593 Dec 18 '19 at 16:25
Yes, I didn't read further in the error message. – Barmar Dec 18 '19 at 16:48

MySQL ERROR: 1005 Can't create table (errno : 150)

1 Answers1