CODEWITHSUNDEEP
mysql
Alexandra Varinechea
Alexandra Varinechea

Reputation: 21

How to debug an error of "SQL state 42000: Key column 'categoria_marca' doesn't exist in table"

This is accompanied by error code 1072.

create database ecommerce_db;

CREATE TABLE tbl_categoria(
codigo INT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(30) unique,
visible boolean default true,
categoria_superior INT,
FOREIGN KEY(categoria_superior) REFERENCES tbl_categoria(codigo)
);

CREATE TABLE tbl_marca(
codigo INT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(30) unique,
visible boolean default true
);

CREATE TABLE tbl_producto(
webid INT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(30),
precio DECIMAL(10,2),
precionuevo DECIMAL(10,2),
stock INT default 1,
nuevo boolean default true,
recomendado boolean default false,
descripcion VARCHAR(255),
visible boolean default true,
CHECK (precio>precionuevo),
codigo_marca INT,
codigo_categoria INT,
FOREIGN KEY(categoria_marca) REFERENCES tbl_marca(codigo),
FOREIGN KEY(categoria_categoria) REFERENCES tbl_categoria(codigo),
img VARCHAR(100) default 'demo.png'
);

Upvotes: 2

Views: 72

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

Your foreign key creation syntax is off. You should specify the following four pieces of information:

  • foreign key name
  • name of column in table which serves as the key
  • target table for the foreign key
  • name of primary/unique column in other table

The major problem I see with your foreign key constraints is that you never link them to any columns in the tbl_producto table.

CREATE TABLE tbl_producto (
    webid INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(30),
    precio DECIMAL(10, 2),
    precionuevo DECIMAL(10, 2),
    stock INT default 1,
    nuevo boolean default true,
    recomendado boolean default false,
    descripcion VARCHAR(255),
    visible boolean default true,
    CHECK (precio > precionuevo),
    codigo_marca INT,
    codigo_categoria INT,
    img VARCHAR(100) default 'demo.png',
    FOREIGN KEY categoria_marca(codigo_marca) REFERENCES tbl_marca(codigo),
    FOREIGN KEY categoria_categoria(codigo_categoria) REFERENCES tbl_categoria(codigo)
);

Side note: MySQL currently does not enforce check constraints, so your CHECK clause will be ignored. You might have to use triggers as a workaround.

Upvotes: 3

Related Questions