Erro ao criar tabela com foreign key no MySQL

Infraestrutura Banco de Dados
#1

Olá, estou tentando criar uma tabela mas o seguinte erro aparece:

Query execution failed

Reason:
SQL Error [1005] [HY000]: Can’t create table casa_leilao.leilao (errno: 150 “Foreign key constraint is incorrectly formed”)

Segue o sql:

create database casa_leilao;
use casa_leilao;

create table operador(
id_operador int not null auto_increment primary key,
nome_operador varchar(45));

insert into operador (id_operador,nome_operador)
values (0,‘Marieta’), (0,‘marcos’);

create table produto(
id_produto int not null auto_increment primary key,
descricao_produto varchar(45)
);

insert into produto (id_produto,descricao_produto)
values (0,‘Cadeira’), (0,‘Mesa’), (0,‘Carro’), (7,‘Moto’);

select * from produto;

create table usuario (
id_usuario int not null auto_increment primary key
);

insert into usuario (id_usuario) values (1),(2);
select * from usuario;

CREATE TABLE leilao(
id_leilao int auto_increment NOT null primary key,
data_leilao date,
id_operador int not null,
nome_operador varchar(45),
INDEX fk_id_operador (id_operador ASC),
INDEX fk_nome_operador (nome_operador ASC),
CONSTRAINT fk_id_operador FOREIGN KEY (id_operador) REFERENCES operador (id_operador),
CONSTRAINT fk_nome_operador FOREIGN KEY (nome_operador) REFERENCES operador (id_operador),
cidade_leilao varchar(45),
id_produto int not null,
descricao_produto varchar(45),
INDEX fk_id_produto (id_produto ASC),
INDEX fk_descricao_produto (descricao_produto ASC),
CONSTRAINT fk_id_produto FOREIGN KEY (id_produto) REFERENCES produto (id_produto),
CONSTRAINT fk_descricao_produto FOREIGN KEY (descricao_produto) REFERENCES produto (descricao_produto),
valor_inicial int,
valor_maior int,
id_usuario_lance int not null,
INDEX fk_id_usuario_lance (id_usuario_lance ASC),
CONSTRAINT fk_id_usuario_lance FOREIGN KEY (id_usuario_lance) REFERENCES usuario (id_usuario),
data_lance date,
id_usuario_ganhador int not null,
INDEX fk_id_usuario_ganhador (id_usuario_ganhador ASC),
CONSTRAINT fk_id_usuario_ganhador FOREIGN KEY (id_usuario_ganhador) REFERENCES usuario (id_usuario)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

Alguém sabe oque estou fazendo de errado?

#2

Por que referenciar a descrição do produto se você já está referenciando o ID do mesmo ?
Nesse caso, se você pretende colocar os dois campos da tabela PRODUTO na tabela LEILÃO, a tabela PRODUTO se torna inútil. Creio eu, que o correto seria referenciar apenas o ID, e trazer a descrição por meio de um select com join.

1 curtida
#3

Bom dia,

escrevi seu codigo com algumas observações que achei relevante, porem na tabela leilão o mais correto é dividir em 2 tabelas tipo
tabela produto_leilao;
tabela lance_produto;

CREATE DATABASE casa_leilao;.
USE casa_leilao;
CREATE TABLE operador(
id_operador int primary key identity, /identity é mesma coisa que primary key, se ele é automatico não vai ser nulo/
nome_operador varchar(45));
INSERT INTO operador (nome_operador) values (‘Marcos’);
CREATE TABLE produto(
id_produto int primary key identity,
descricao_produto varchar(45));
INSERT INTO produto (descricao_produto) VALUES (‘cadeira’),(‘mesa’),(‘carro’),(‘moto’)
SELECT * FROM produto;
SELECT * FROM operador;
CREATE TABLE usuario(
id_usuario int primary key identity);
ALTER TABLE usuario /não consegui realizar o insert na tabela usuario somente com o id /
add nome_usuario varchar(45); /alterei a tabela com mais um campo/
CREATE TABLE usuario2(
id_usuario int primary key); / outra opição é colocar o id como não sendo auto incremente/
INSERT INTO usuario2 (id_usuario) VALUES (1),(2);
SELECT * FROM usuario2;
CREATE TABLE leilao(
id_leilao int primary key identity,
data_leilao date,
nome_operador varchar(45),
/nome_operar varchar(45) references operador(id_operador) “esta comando não é valido, pois quando se faz referencia a uma chave estrangeira
os dois campos tem que ser iguais”/
/para uma futura consulta para saber o nome do operador o melhor a fazer é um inner join/
cidade_leilao varchar(45),
/descricao_produto varchar(45) “mesma situação”/
valor_inicial int, /o mais indicado seria double ou decimal/
valor_maior int, /mesma situação/
id_usuario_lance int references usuario2(id_usuario),
data_lance date,
id_operador int references operador(id_operador),
id_produto int references produto(id_produto),
id_usuario_ganhador int references usuario2(id_usuario));

1 curtida
#4

Verdade, eu estava sendo redundante.
Fiz desse jeito:

create database casa_leilao;
use casa_leilao;

create table operador(
id_operador int not null auto_increment primary key,
nome_operador varchar(45));

insert into operador (id_operador,nome_operador)
values (0,‘Marieta’), (0,‘marcos’);

create table produto(
id_produto int not null auto_increment primary key,
descricao_produto varchar(45)
);

insert into produto (id_produto,descricao_produto)
values (0,‘Cadeira’), (0,‘Mesa’), (0,‘Carro’), (7,‘Moto’);

select * from produto;

create table usuario (
id_usuario int not null auto_increment primary key
);

insert into usuario (id_usuario) values (1),(2);
select * from usuario;

CREATE TABLE leilao(
id_leilao int auto_increment NOT null primary key,
data_leilao date,
id_operador int not null,
cidade_leilao varchar(45),
CONSTRAINT fk_id_operador FOREIGN KEY (id_operador) REFERENCES operador (id_operador));

CREATE TABLE lance (
id_lance int(11) NOT NULL AUTO_INCREMENT,
id_produto int(11) DEFAULT NULL,
valor_inicial int(11) DEFAULT NULL,
valor_maior int(11) DEFAULT NULL,
data_lance date DEFAULT NULL,
id_ganhador int(11) DEFAULT NULL,
id_leilao int(11) DEFAULT NULL,
PRIMARY KEY (id_lance),
KEY fk_id_ganhador (id_ganhador),
KEY fk_id_leilao (id_leilao),
CONSTRAINT fk_id_ganhador FOREIGN KEY (id_ganhador) REFERENCES usuario (id_usuario),
CONSTRAINT fk_id_leilao FOREIGN KEY (id_leilao) REFERENCES leilao (id_leilao) ON DELETE NO ACTION ON UPDATE NO ACTION
);

#5

Muito obrigado pela ajuda! depois de ver o seu sql entendi oque estava fazendo de errado.