Page 1 of 1

MySQL error 150 problem - foreign key

Posted: Tue Jun 16, 2009 9:52 am
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 ?

Re: MySQL error 150 problem - foreign key

Posted: Tue Jun 16, 2009 10:03 am
by VladSun

Re: MySQL error 150 problem - foreign key

Posted: Tue Jun 16, 2009 11:06 am
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 ?

Re: MySQL error 150 problem - foreign key

Posted: Wed Jun 17, 2009 3:18 am
by VladSun
Try to explicitly set (at create time) InnoDB type to *both* tables.

Re: MySQL error 150 problem - foreign key

Posted: Wed Jun 17, 2009 6:38 am
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 :)