Table 'tableName' contains a constraint definition with column 'columnName' which is not in the table Java Derby

Question

I am trying to run a SQL script to my database created on Java Derby:

    CREATE TABLE USUARIO (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    EMAIL VARCHAR(40) NOT NULL UNIQUE,
    NOMBRES VARCHAR(20) NOT NULL,
    APELLIDOS VARCHAR(20) NOT NULL,
    CONTRASEÑA VARCHAR(20) NOT NULL,
    CEDULA INTEGER,
    TELEFONO INTEGER,

    CONSTRAINT ID_USUARIO_PK PRIMARY KEY (ID) -- Primary Key
);

CREATE TABLE ORGANIZACION (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, -- Id autonumérico
    NOMBRE VARCHAR(20) NOT NULL,

    CONSTRAINT ID_ORGANIZACION_PK PRIMARY KEY (ID),
    CONSTRAINT ID_DIRECCION_ORG_FK FOREIGN KEY (DIRECCION_ORG) REFERENCES DIRECCION_ORG (ID),
    CONSTRAINT ID_TELEFONO_ORG_FK FOREIGN KEY (TELEFONO_ORG) REFERENCES TELEFONO_ORG (ID)
);

CREATE TABLE TELEFONO_ORG (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, -- Id autonumérico
    TELEFONO INTEGER NOT NULL,

    CONSTRAINT ID_TELEFONO_ORG_PK PRIMARY KEY (ID)
);

CREATE TABLE DIRECCION_ORG (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, -- Id autonumérico
    DIRECCION VARCHAR(300) NOT NULL,

    CONSTRAINT ID_DIRECCION_ORG_PK PRIMARY KEY (ID)
);

CREATE TABLE PRODUCTO (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    RUTA_IMAGEN VARCHAR(400) NOT NULL,
    NOMBRE VARCHAR(20) NOT NULL,
    CANTIDAD INTEGER,
    PRECIO INTEGER,
    COSTO INTEGER,

    CONSTRAINT ID_PRODUCTO_PK PRIMARY KEY (ID), -- Primary Key
    CONSTRAINT ID_ORGANIZACION_FK FOREIGN KEY (ORGANIZACION) REFERENCES ORGANIZACION (ID)
);

But I am getting this error:

Table ORGANIZACION contains a constraint definition with column DIRECCION_ORG which is not in the table.

What can be wrong here?

Answer 1

You are making constraints to foreign keys but haven't actually created the columns to contain those keys yet. Make those columns and make the constraints point to them (instead of just using the table's name again). Shown below with -- comments indicating where to add it.

CREATE TABLE USUARIO (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    EMAIL VARCHAR(40) NOT NULL UNIQUE,
    NOMBRES VARCHAR(20) NOT NULL,
    APELLIDOS VARCHAR(20) NOT NULL,
    CONTRASEÑA VARCHAR(20) NOT NULL,
    CEDULA INTEGER,
    TELEFONO INTEGER,

    CONSTRAINT ID_USUARIO_PK PRIMARY KEY (ID) -- Primary Key
);

CREATE TABLE ORGANIZACION (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, -- Id autonumérico
    NOMBRE VARCHAR(20) NOT NULL,
    DIRECCION_ORG_ID INTEGER NOT NULL,   -- ADD THIS and change constraint FK name
    TELEFONO_ORG_ID INTEGER NOT NULL,   -- ADD THIS and change constraint FK name

    CONSTRAINT ID_ORGANIZACION_PK PRIMARY KEY (ID),
    CONSTRAINT ID_DIRECCION_ORG_FK FOREIGN KEY (DIRECCION_ORG_ID) REFERENCES DIRECCION_ORG (ID),
    CONSTRAINT ID_TELEFONO_ORG_FK FOREIGN KEY (TELEFONO_ORG_ID) REFERENCES TELEFONO_ORG (ID)
);

CREATE TABLE TELEFONO_ORG (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, -- Id autonumérico
    TELEFONO INTEGER NOT NULL,

    CONSTRAINT ID_TELEFONO_ORG_PK PRIMARY KEY (ID)
);

CREATE TABLE DIRECCION_ORG (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, -- Id autonumérico
    DIRECCION VARCHAR(300) NOT NULL,

    CONSTRAINT ID_DIRECCION_ORG_PK PRIMARY KEY (ID)
);

CREATE TABLE PRODUCTO (
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    RUTA_IMAGEN VARCHAR(400) NOT NULL,
    NOMBRE VARCHAR(20) NOT NULL,
    CANTIDAD INTEGER,
    PRECIO INTEGER,
    COSTO INTEGER,
    ORGANIZACION_ID INTEGER NOT NULL, --ADD THIS and change constraint FK name

    CONSTRAINT ID_PRODUCTO_PK PRIMARY KEY (ID), -- Primary Key
    CONSTRAINT ID_ORGANIZACION_FK FOREIGN KEY (ORGANIZACION_ID) REFERENCES ORGANIZACION (ID)
);