db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Error code 42X05. Table doesn't exist.Pls help with code.
Date Mon, 27 Jun 2011 13:48:42 GMT
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
>>> ;
>>
>>


Mime
View raw message