db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From IkeAbalogu <ikeabal...@hotmail.com>
Subject Re: Error code 42X05. Table doesn't exist.Pls help with code.
Date Mon, 27 Jun 2011 13:55:32 GMT

Thanks.For your interest and help.Below is the entire schema


--------------------------------------------------------------
-- Database creation Script
--------------------------------------------------------------
-- Create a Database table to represent the "product" entity.
CREATE TABLE product(
	name	VARCHAR(30) NOT NULL,
	description	LONG VARCHAR NOT NULL,
	source	LONG VARCHAR NOT NULL,
	-- Specify the PRIMARY KEY constraint for table "product".
	-- This indicates which attribute(s) uniquely identify each row of data.
	CONSTRAINT	pk_product PRIMARY KEY (name)
);

-- 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)
);


--------------------------------------------------------------
-- Alter Tables to add fk constraints --

-- Now all the tables have been created the ALTER TABLE command is used to
define some additional
-- constraints.  These typically constrain values of foreign keys to be
associated in some way
-- with the primary keys of related tables.  Foreign key constraints can
actually be specified
-- when each table is created, but doing so can lead to dependency problems
within the script
-- i.e. tables may be referenced before they have been created.  This method
is therefore safer.

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

-- This constraint ensures that the foreign key of table "producttank"
-- correctly references the primary key of table "product"

ALTER TABLE producttank ADD CONSTRAINT fk1_producttank_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
"productsupply_product" relationship

-- 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;
--------------------------------------------

Thanks.


Rick Hillegas-3 wrote:
> 
> 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
>>>> ;
>>>
>>>
> 
> 
> 

-- 
View this message in context: http://old.nabble.com/Error-code-42X05.-Table-doesn%27t-exist.Pls-help-with-code.-tp31936869p31937684.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message