Re: Error code 42X05. Table doesn't exist.Pls help with code.

[Rick Hillegas]

When I run these commands, I get an error on this one:
ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product
FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE
RESTRICT;
ERROR X0Y46: Constraint 'FK1_PRODUCTSUPPLY_TO_PRODUCT' is invalid: 
referenced table PRODUCT does not exist.

That seems reasonable because I don't see a table called PRODUCT in this 
schema. Can you share the rest of your schema with us?

Thanks,
-Rick

On 6/27/11 6:22 AM, IkeAbalogu wrote:
Thanks for your response.I get Error code 42X05. Table/view doesn't exist
-- Create a Database table to represent the "productsupply" entity.
CREATE TABLE productsupply(
        supplyid        INTEGER NOT NULL,
        supply_date     DATE NOT NULL,
        isfinished      SMALLINT NOT NULL,
        finish_date     DATE NOT NULL,
        quantity        BIGINT NOT NULL,
        costprice       DOUBLE PRECISION NOT NULL,
        saleprice       DOUBLE PRECISION NOT NULL,
        fk1_name        VARCHAR(30) NOT NULL,
        -- Specify the PRIMARY KEY constraint for table "productsupply".
        -- This indicates which attribute(s) uniquely identify each row of data.
        CONSTRAINT      pk_productsupply PRIMARY KEY (supplyid)
);

-- Create a Database table to represent the "productquantmonitor" entity.
CREATE TABLE productquantmonitor(
        checkdate       DATE NOT NULL,
        tanknumber      INTEGER NOT NULL,
        balance BIGINT NOT NULL,
        previous_balance        BIGINT NOT NULL,
        quantitysold    BIGINT NOT NULL,
        fk1_supplyid    INTEGER NOT NULL
);

-- Create a Database table to represent the "producttank" entity.
CREATE TABLE producttank(
        tankid  INTEGER NOT NULL,
        tankcapacity    BIGINT NOT NULL,
        fk1_name        VARCHAR(30) NOT NULL,
        -- Specify the PRIMARY KEY constraint for table "producttank".
        -- This indicates which attribute(s) uniquely identify each row of data.
        CONSTRAINT      pk_producttank PRIMARY KEY (tankid)
);
-- This constraint ensures that the foreign key of table "productsupply"
-- correctly references the primary key of table "product"

ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product
FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE
RESTRICT;

-- Alter table to add new constraints required to implement the
"productquantmonitor_productsupply" relationship

-- This constraint ensures that the foreign key of table
"productquantmonitor"
-- correctly references the primary key of table "productsupply"

ALTER TABLE productquantmonitor ADD CONSTRAINT
fk1_productquantmonitor_to_productsupply FOREIGN KEY(fk1_supplyid)
REFERENCES productsupply(supplyid) ON DELETE RESTRICT ON UPDATE RESTRICT;


Rick Hillegas-3 wrote:
Can you share your schema and the error message you are seeing? That
will help people advise you.

Thanks,
-Rick

On 6/27/11 5:20 AM, IkeAbalogu wrote:
CREATE TRIGGER NEWBALANCE
AFTER INSERT ON APP.PRODUCTQUANTMONITOR
REFERENCING NEW AS NEWROW
FOR EACH ROW
UPDATE NEWROW SET PREVIOUS_BALANCE =
CASE
WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) =
1
THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID =
NEWROW.FK1_SUPPLYID)
ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE
FK1_SUPPLYID = NEWROW.FK1_SUPPLYID
AND TANKNUMBER = NEWROW.TANKNUMBER)
END
;