Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    New Coder
    Join Date
    Jun 2007
    Posts
    38
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Error on table creation - #1005 - (Errcode: 150)

    Hello everybody. triyng to develop my acknowledges on mysql.

    setting foreign keys on a table countries and users and now i have an error on PHPMyadmin.
    The data base is "com"
    "#1005 - cannot create table 'com.users' (Errcode: 150) "

    here's the sql script
    Code:
    -- ----------------------------------------------------------------------
    SET FOREIGN_KEY_CHECKS = 0;
    -- -------------------------------------
    -- Tables
    
    DROP TABLE IF EXISTS `categorias`;
    CREATE TABLE `categorias` (
      `cat_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `cat_nome` VARCHAR(32)  NOT NULL,
      PRIMARY KEY (`cat_id`)
    )
    ENGINE = InnoDB
    CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    
    DROP TABLE IF EXISTS `artigos`;
    CREATE TABLE `artigos` (
      `art_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `art_nome` VARCHAR(32) NOT NULL,
      `art_desc` VARCHAR(80) NOT NULL,
      `cat_id` INT(11) UNSIGNED NOT NULL,
      PRIMARY KEY (`art_id`),
      CONSTRAINT `FKcategorias` FOREIGN KEY `FKcategorias` (`cat_id`)
        REFERENCES `categorias` (`cat_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    )
    ENGINE = InnoDB
    CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    
    DROP TABLE IF EXISTS `Imagens`;
    CREATE TABLE `Imagens` (
    	`imagem_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`imagem_nome` VARCHAR(32) NOT NULL,
    	`imagem_data` DATETIME NOT NULL,
    	`art_id` INT(11) UNSIGNED NOT NULL,
    	PRIMARY KEY (`imagem_id`),
    CONSTRAINT `FKartigos` FOREIGN KEY `FKartigos` (`art_id`)
        REFERENCES `artigos` (`art_id`)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
    )
    ENGINE = InnoDB
    CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    
    
    DROP TABLES IF EXISTS `Paises`;
    CREATE TABLE Paises (
    	`id_pais` INT(11) NOT NULL,
    	`nome` varchar(50) NOT NULL
    ) 
    ENGINE = InnoDB
    CHARACTER SET latin1;
    
    DROP TABLES IF EXISTS `Users`;
    CREATE TABLE `users` (
    	`user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`first_name` VARCHAR(20) NOT NULL,
    	`last_name` VARCHAR(40) NOT NULL,
    	`phone` VARCHAR(30) NOT NULL,
    	`email` VARCHAR(60) NOT NULL,
    	`pais` VARCHAR(50) NOT NULL,
    	`registo` DATETIME NOT NULL,
    	`id_pais` INT(11) UNSIGNED NOT NULL,
    	PRIMARY KEY (`user_id`),
    CONSTRAINT `FKpaises` FOREIGN KEY `FKpaises` (`id_pais`)
    	REFERENCES `Paises` (`id_pais`)
    	ON DELETE NO ACTION
    	ON UPDATE CASCADE
    )
    ENGINE = InnoDB
    CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    
    
    SET FOREIGN_KEY_CHECKS = 1;
    Thank u for ure advices.

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Code:
    DROP TABLES IF EXISTS `Users`;
    CREATE TABLE `users` (
    	`user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`first_name` VARCHAR(20) NOT NULL,
    	`last_name` VARCHAR(40) NOT NULL,
    	`phone` VARCHAR(30) NOT NULL,
    	`email` VARCHAR(60) NOT NULL,
    	`pais` VARCHAR(50) NOT NULL,
    	`registo` DATETIME NOT NULL,
    	`id_pais` INT(11) UNSIGNED NOT NULL,
    	PRIMARY KEY (`user_id`),
    CONSTRAINT `FKpaises` FOREIGN KEY `FKpaises` (`id_pais`)
    	REFERENCES `Paises` (`id_pais`)
    	ON DELETE NO ACTION
    	ON UPDATE CASCADE
    )
    ENGINE = InnoDB
    CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    
    
    SET FOREIGN_KEY_CHECKS = 1;
    in php identifiers are case sensitive in mysql are not so, at first look, this could be the problem.

    best regards

  • #3
    New Coder
    Join Date
    Jun 2007
    Posts
    38
    Thanks
    1
    Thanked 0 Times in 0 Posts
    tried i get the same error.
    "#1005 - cannot create table 'com.users' (Errcode: 150) "

  • #4
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    I would build your tables differently so that if you ever change a category name (for example), it will change everywhere it is used.

    Code:
    DROP TABLE IF EXISTS categorias;
    CREATE TABLE categorias
    ( cat_nome VARCHAR(32)  NOT NULL
    , PRIMARY KEY (cat_nome)
    )ENGINE = InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
    
    DROP TABLE IF EXISTS artigos;
    CREATE TABLE artigos
    ( art_nome VARCHAR(32) NOT NULL
    , cat_nome VARCHAR(32) NOT NULL
    , art_desc VARCHAR(80) NOT NULL
    , PRIMARY KEY (art_nome,cat_nome)
    , CONSTRAINT FKcategorias 
        FOREIGN KEY (cat_nome)
        REFERENCES categorias (cat_nome)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    )
    ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    
    DROP TABLE IF EXISTS Imagens;
    CREATE TABLE Imagens
    ( imagem_id INT UNSIGNED NOT NULL AUTO_INCREMENT
    , imagem_nome VARCHAR(32) NOT NULL
    , art_nome VARCHAR (32) NOT NULL
    , cat_nome VARCHAR (32) NOT NULL
    , imagem_data DATETIME NOT NULL
    , PRIMARY KEY (imagem_id)
    , CONSTRAINT FKartigos
        FOREIGN KEY (art_nome,cat_nome)
          REFERENCES artigos (art_nome,cat_nome)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
    )
    ENGINE = InnoDB
    CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    
    
    DROP TABLES IF EXISTS paises;
    CREATE TABLE paises 
    ( paises varchar(99) NOT NULL
    , primary key (paises)
    ) 
    ENGINE = InnoDB
    CHARACTER SET latin1;
    
    DROP TABLES IF EXISTS users;
    CREATE TABLE users 
    ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT
    , first_name VARCHAR(20) NOT NULL
    , last_name VARCHAR(40) NOT NULL
    , phone VARCHAR(30) NOT NULL
    , email VARCHAR(60) NOT NULL
    , pais VARCHAR(99) NOT NULL
    , registo DATETIME NOT NULL
    , PRIMARY KEY (user_id)
    , CONSTRAINT FKpaises
        FOREIGN KEY (pais)
          REFERENCES paises(paises)
    	ON DELETE NO ACTION
    	ON UPDATE CASCADE
    )
    points to see:
    1. no need for a primary key (int) if the varchar is unique.
    2. see the composite keys (art_nome,cat_nome). they make use of two cols which, together, are unique.

    hth

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • Users who have thanked bazz for this post:

    Cyber_type (04-30-2009)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    OESXYL: My SQL table names *ARE* case sensitive, except when MySQL is used on Windows (and maybe on a few other OS's). That's because MySQL creates operating system *files* that underly the tables that are the same names (or derived from) the table names. And of course file names *are* case sensitive on Linux/Unix file systems.

    So your hint was most probably correct. It's unlikely he is using MySQL/PHP on a Windows machine.

  • #6
    New Coder
    Join Date
    Jun 2007
    Posts
    38
    Thanks
    1
    Thanked 0 Times in 0 Posts
    yes indeed using phpmyadmin on a windows machine. But i think case sensitive or not it is better to practice allways like it was case sensitive
    with the old table i was doing table jointures like that
    Code:
    "SELECT art_nome, art_desc, cat_nome FROM artigos INNER JOIN categorias ON artigos.cat_id = categorias.cat_id"
    so my tried with new table to doing almost same thing

    Code:
    "SELECT art_nome, cat_nome FROM artigos INNER JOIN categorias ON artigos.cat_nome = categorias.cat_nome;"
    the query gives me an error
    "ERROR 1052 (23000): field: 'cat_nome' in field list is ambiguous"
    ps. translated from french think is correct
    "Error 1052" (23000):Champ: 'cat_nome' dans field listest ambigu"
    Thank u a lot for ure advices

  • #7
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    when you select the fields, they must be alised the same way as in your ON clause.

    So if you on clause has ON artigos.cat_nome = categorias.cat_nome your initial select query has to have artigos.art_nome, categorias.cat_nome like this

    Code:
    (" select 
      a.art_nome
    , c.cat_nome
    FROM artigos as a
    INNER 
    JOIN categorias as c
    ON a.cat_nome = c.cat_nome
    ) or die "bleh";
    Doing so makes it clear which table has the column you want and makes consistent which value you are asking for. Imagine if you had the same col name in more than one table (queried). MySQL needs to know which col you are asking for from which table.

    With regard to case sensitivity, I find it much easier to stick to one case either upper or lower, throughout. Had a whole heap of mess and time wasting when I tried to set up any other protocol in my head. Even I can remember something that simple.
    hth

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #8
    New Coder
    Join Date
    Jun 2007
    Posts
    38
    Thanks
    1
    Thanked 0 Times in 0 Posts
    ure great! thank u!
    if i would to include on my INNER JOIN another table how i would make my query?
    sure that i must put another field from the another table (categorias) into "SELECT"
    ex:"SELECT imagens.imagem_id, imagens.imagem_nome, artigos.art_nome, categorias.cat_nome FROM imagens INNER JOIN ....."

    how we use INNER JOIN if we want to make queries trough 3 or more tables?

  • #9
    New Coder
    Join Date
    Jun 2007
    Posts
    38
    Thanks
    1
    Thanked 0 Times in 0 Posts
    tried this

    Code:
    SELECT imagens.imagem_id, imagens.imagem_nome, artigos.art_nome, categorias.cat_nome FROM imagens INNER JOIN artigos ON imagens.art_nome = artigos.art_nome INNER JOIN categorias ON imagens.imagem_id = categorias.cat_nome;
    works but returns me "empty set" wich is not correct. Make a simple "Select * from imagens;" and returns 3 values.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Ummm...
    Code:
    SELECT imagens.imagem_id, imagens.imagem_nome, artigos.art_nome, categorias.cat_nome 
    FROM imagens INNER JOIN artigos ON imagens.art_nome = artigos.art_nome 
    INNER JOIN categorias ON imagens.imagem_id = categorias.cat_nome;
    Do you REALLY have values of imagem_id that are the SAME as values of cat_nome???

    Surely imagens.imagem_id is a *number* but categorias.cat_nome is a *string*. No?

    If so, then of course you have no matches and so of course you get an empty set.

  • #11
    New Coder
    Join Date
    Jun 2007
    Posts
    38
    Thanks
    1
    Thanked 0 Times in 0 Posts
    ok have understanded what u mean and my problem
    Code:
    SELECT categorias.cat_nome, imagens.imagem_nome, artigos.art_nome FROM imagens INNER JOIN categorias ON imagens.cat_nome = categorias.cat_nome INNER JOIN artigos ON imagens.art_nome = artigos.art_nome;
    thanks to you

  • #12
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Just to follow up on the original question and point out why the error happened:

    Code:
    	`id_pais` INT(11) NOT NULL,
    	`nome` varchar(50) NOT NULL
    ) 
    
    	`id_pais` INT(11) UNSIGNED NOT NULL,
    	PRIMARY KEY (`user_id`),
    CONSTRAINT `FKpaises` FOREIGN KEY `FKpaises` (`id_pais`)
    both column types must be the same between the foreign key and original table. You are using an UNSIGNED INT in your second table while the column it refers to is an INT data type.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    GREAT EYES!

    I must have looked at that four or five times and never noticed it.

  • #14
    New to the CF scene
    Join Date
    May 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Drop Table to scroll. time slot incripted. scroll again. enter time.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •