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.
