Can't create table (errno: 150) on FOREIGN KEY

Question

I saw a lot of same question but I couldn't solve my case.

If I run this code:

<?php
include_once($_SERVER['DOCUMENT_ROOT'].'/config.php');

$servername = HOST;
$username = USERNAME;
$password = PASSWORD;
$dbname = DB;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

// sql to create table
$sql = "CREATE TABLE IF NOT EXISTS Articls (
            id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,     
            name VARCHAR(254) COLLATE utf8_persian_ci NOT NULL      
) DEFAULT COLLATE utf8_persian_ci";

if ($conn->query($sql) === TRUE) {
    echo "Table Articls created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

/////////////////////////////////////////////////////////////////////////

$sql = "CREATE TABLE IF NOT EXISTS Tags (
            id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,  
            id_articls INT(10) UNSIGNED NOT NULL,   
            name VARCHAR(256) COLLATE utf8_persian_ci NOT NULL,           
            FOREIGN KEY(Tags.id_articls) REFERENCES Articls(Articls.id)         
) DEFAULT COLLATE utf8_persian_ci";

if ($conn->query($sql) === TRUE) {
    echo "Table Tags created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

I get this error: ( If I remove FOREIGN KEY it works)

Table Articls created successfully Error creating table: Can't create table 'admin_wepar.Tags' (errno: 150)

Edit

If a change into Articls.id and Tags.id_articls I got this error:

Table Articls created successfullyError creating table: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY (Tags.id_articls) REFERENCES Articls(Articls.id) ) DEFAULT COLLA' at line 5

Your first create table statement would fail, because of the comma on the second line. — Gordon Linoff, Dec 28 '14 at 19:41
You are right but the orginal code is true...I also corrected here — ali raha, Dec 28 '14 at 19:43
You are also using `create table if not exists`, so you do not know if the table is really being created. Try dropping the table and running the code again. — Gordon Linoff, Dec 28 '14 at 19:45

Tomas Creemers · Answer 1 · 2014-12-29T20:57:39.833

0

Tags.id_articls is a signed integer while Articl.id is an unsigned integer. MySQL requires referencing field to be exactly the same type. Make Tags.id_articls unsigned to have it work.

Additionally, the table names in the column lists are not allowed in MySQL. It is always clear which table is meant: first the referencing table and then the referenced table. So change

FOREIGN KEY(Tags.id_articls) REFERENCES Articls(Articls.id)

into

FOREIGN KEY(id_articls) REFERENCES Articls(id)

and it will work.

edited Dec 29 '14 at 20:57

answered Dec 28 '14 at 19:45

Tomas Creemers

2,645
14
15

I changed the code to "id_articls INT(10) UNSIGNED NOT NULL," but I get last error (check my edited question" – ali raha Dec 28 '14 at 20:02
@aliraha: I updated my answer to solve the remaining problem. – Tomas Creemers Dec 29 '14 at 20:58

score 0 · Answer 2 · answered Dec 28 '14 at 19:45

0

You need to declare both Articls.id and Tags.id_articls signed or unsigned

answered Dec 28 '14 at 19:45

Lennart - Slava Ukraini

6,936
1
20
32

I changd it but I get last error (check my edited question" pls – ali raha Dec 28 '14 at 20:03
Beats me, I copy pasted your sql code into mysql (except for making id_articls unsigned) and it worked for me. – Lennart - Slava Ukraini Dec 28 '14 at 20:11
You should edit your question so it contains the correct type for id_articls. – Lennart - Slava Ukraini Dec 28 '14 at 20:13
I change the source (Edited question) and copy/paste..I get same error – ali raha Dec 28 '14 at 20:21

Can't create table (errno: 150) on FOREIGN KEY

2 Answers2

Linked

Related