...

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



Cyber_type
04-30-2009, 11:06 AM
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


-- ----------------------------------------------------------------------
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.

oesxyl
04-30-2009, 11:32 AM
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

Cyber_type
04-30-2009, 12:46 PM
tried i get the same error.
"#1005 - cannot create table 'com.users' (Errcode: 150) "

bazz
04-30-2009, 01:53 PM
I would build your tables differently so that if you ever change a category name (for example), it will change everywhere it is used.



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

Old Pedant
04-30-2009, 08:03 PM
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.

Cyber_type
05-03-2009, 02:46 PM
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:thumbsup:
with the old table i was doing table jointures like that


"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



"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

bazz
05-03-2009, 03:54 PM
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



(" 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

Cyber_type
05-03-2009, 05:15 PM
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?

Cyber_type
05-03-2009, 07:35 PM
tried this



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.

Old Pedant
05-03-2009, 08:51 PM
Ummm...


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.

Cyber_type
05-04-2009, 02:09 PM
ok have understanded what u mean and my problem :o


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:thumbsup:

guelphdad
05-04-2009, 06:20 PM
Just to follow up on the original question and point out why the error happened:



`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.

Old Pedant
05-04-2009, 06:26 PM
GREAT EYES!

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

ANTHONY RONCHI
05-04-2009, 06:47 PM
Drop Table to scroll. time slot incripted. scroll again. enter time.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum