MySQL error 150 problem - foreign key

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Wolfie
Forum Commoner
Posts: 37
Joined: Wed Jan 28, 2009 8:40 am
Location: Poland

MySQL error 150 problem - foreign key

Post by Wolfie »

Hi all,

I am reading the book php5 pro and I have stacked on one chapter because of error 150.

I have build the database managercontact and the new table which looks like this :

Code: Select all

 
<?php
 
    require('conn.php');
    
    $sql = "CREATE TABLE jednostka (
        jednostka_id SERIAL PRIMARY KEY NOT NULL,
        snazwa1 varchar(100) NOT NULL,
        snazwa2 varchar(100) NOT NULL,
        ctyp char(1) NOT NULL
        )";
            
    $query = mysql_query($sql) or die(mysql_error());
    echo 'table created!';
?>
 
Now after creating this table I am doing another query which looks like this :

Code: Select all

 
<?php
 
    require('conn.php');
    
    
    
    $sql = "CREATE TABLE adres (
        adres_id SERIAL PRIMARY KEY NOT NULL,
        jednostka_id int,
        sadres1 varchar(255),
        sadres2 varchar(255),
        smiasto varchar(255),
        skod varchar(10),
        styp varchar(50),
        CONSTRAINT fk_adres_jednostka_id
            FOREIGN KEY (jednostka_id) REFERENCES jednostka(jednostka_id)
        )";
        
    $query = mysql_query($sql) or die(mysql_error());
?>
 
The problem is that when I am lunching second script I get this error :
Can't create table 'contactmanager.adres' (errno: 150)
Even after I am adding this line of code

Code: Select all

 
$query = mysql_query('SET FOREIGN_KEY_CHECKS=0') or die(mysql_error())
 
to second script the error still apears.

During reserching the problem I was also trying to do the second table first with SET FOREIGN_KEY_CHECKS=0 and than first script. In this case the second table query is correct but then after lunching first script the error aperas.I was also trying to add the line FOREIGN_KEY_CHECKS to first script but with no results.

Any ideas how to fix it ?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL error 150 problem - foreign key

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
Wolfie
Forum Commoner
Posts: 37
Joined: Wed Jan 28, 2009 8:40 am
Location: Poland

Re: MySQL error 150 problem - foreign key

Post by Wolfie »

Well it helps a little but the link inside your link does't work, so there is only a part of solution.....

After some lecture of databeses I was trying to do something like this :

first script :

Code: Select all

 
<?php
 
    require('conn.php');
    
    $sql = "CREATE TABLE jednostka (
        jednostka_id SERIAL PRIMARY KEY NOT NULL,
        snazwa1 varchar(100) NOT NULL,
        snazwa2 varchar(100) NOT NULL,
        ctyp char(1) NOT NULL
        )";
            
    $query = mysql_query($sql) or die(mysql_error());
    
    echo 'Tabela utworzona!';
?>
 
Everything goes good and than second modified script with INDEX :

Code: Select all

 
<?php
 
    require('conn.php');
    
    $sql = "CREATE TABLE adres (
        adres_id SERIAL PRIMARY KEY NOT NULL,
        jednostka_id int,
        sadres1 varchar(255),
        sadres2 varchar(255),
        smiasto varchar(255),
        skod varchar(10),
        styp varchar(50),
        INDEX jedn_id (jednostka_id),
        CONSTRAINT fk_adres_jednostka_id
            FOREIGN KEY (jednostka_id) REFERENCES jednostka(jednostka_id)
        )";
        
    $query = mysql_query($sql) or die(mysql_error());
        
?>
 
But still the same error.....
Can't create table 'contactmanager.adres' (errno: 150)
any more help ?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL error 150 problem - foreign key

Post by VladSun »

Try to explicitly set (at create time) InnoDB type to *both* tables.
There are 10 types of people in this world, those who understand binary and those who don't
Wolfie
Forum Commoner
Posts: 37
Joined: Wed Jan 28, 2009 8:40 am
Location: Poland

Re: MySQL error 150 problem - foreign key

Post by Wolfie »

I have figure out the problem.


The thing was that SERIAL in MySQL is BIGINT. So after creating PRIMARY KEY in first table it gets BIGINT. Than in second table the FOREIGN KEY has type INT so the types are not equal and this was a reason of the error.

The proper solution is :

Code: Select all

 
<?php
 
    require('conn.php');
    
    $sql = "CREATE TABLE jednostka (
        jednostka_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
        snazwa1 varchar(100) NOT NULL,
        snazwa2 varchar(100) NOT NULL,
        ctyp char(1) NOT NULL
        )";
            
    $query = mysql_query($sql) or die(mysql_error());
    
    echo 'Tabela utworzona!';
?>
 
And than the second table :

Code: Select all

 
<?php
 
    require('conn.php');
    
    $sql = "CREATE TABLE adres (
        adres_id int PRIMARY KEY AUTO_INCREMENT NOT NULL,
        jednostka_id int,
        sadres1 varchar(255),
        sadres2 varchar(255),
        smiasto varchar(255),
        skod varchar(10),
        styp varchar(50),
        INDEX jedn_ind (jednostka_id),
        CONSTRAINT fk_adres_jednostka_id
            FOREIGN KEY (jednostka_id) REFERENCES jednostka(jednostka_id)
            ON DELETE CASCADE
        )ENGINE=INNODB";
        
    
    //$query = mysql_query('SET FOREIGN_KEY_CHECKS=0') or die(mysql_error());
    $query = mysql_query($sql) or die(mysql_error());
        
    echo 'Jest super!';
?>
 
ON DELETE CASCADE and ENGINE=INNODB in second table is not necessary if u have set this value as default.
INDEX is also needless.

Regards for all :)
Post Reply