Thanks Gunnar
I will be a monkeys uncle, that worked.
ALTER TABLE `IHTemp` ADD FOREIGN KEY +
( `CustID` )+
REFERENCES `Customer` +
('Cannot insert - value does not exist in Customer',+
'Cannot update - value does not exist in Customer') NOCHECK
Marc
--------------------------------------------------
From: "Gunnar Ekblad" <[email protected]>
Sent: Tuesday, March 16, 2010 1:18 PM
To: "RBASE-L Mailing List" <[email protected]>
Subject: [RBASE-L] - Re: Temp Tables
Marc
Please try the alter table command with the NOCHECK option
A long time ago I had issue with temp tables on this where I tried to
rename
a column and was given an error message. I submitted an enhancement
request
and suddenly I had the NOCHECK option that handled my issue. It might work
for you as well
Gunnar Ekblad
Kontema IT AB
Hästholmsvägen 32
131 30 Nacka
Sweden
-----Ursprungligt meddelande-----
Från: [email protected] [mailto:[email protected]] För MDRD
Skickat: den 16 mars 2010 18:12
Till: RBASE-L Mailing List
Ämne: [RBASE-L] - Re: Temp Tables
I did Unload structure for InvoiceHeader in RRBYW14
Change the table name to IHTemp then ran the code below.
I get errors when Adding the FK's
Either I am missing the nose on my face or FK's are not allowed on Temp
Tables?
Marc
DROP TABLE IHTemp
CREATE TABLE `IHTemp` +
(`TransID` INTEGER NOT NULL +
('Value for column TransID cannot be null') , +
`CustID` INTEGER , +
`EmpID` INTEGER , +
`TransDate` DATE , +
`BillToCompany` TEXT (40) , +
`BillToAddress` TEXT (30) , +
`BillToCity` TEXT (20) , +
`BillToState` TEXT (2) , +
`BillToZip` TEXT (10) , +
`ShipToCompany` TEXT (40) , +
`ShipToAddress` TEXT (30) , +
`ShipToCity` TEXT (20) , +
`ShipToState` TEXT (2) , +
`ShipToZip` TEXT (10) , +
`NetAmount` CURRENCY , +
`Freight`= +
( netamount* .01) CURRENCY , +
`Tax`= +
( netamount* .081) CURRENCY , +
`InvoiceTotal`= +
( netamount+ freight+ tax) CURRENCY )
ALTER TABLE `IHTemp` ADD PRIMARY KEY +
(`TransID` ) +
('Values for rows in IHTemp must be unique',+
'Cannot delete - values exist in another table',+
'Cannot update - values exist in another table')
ALTER TABLE `IHTemp` ADD FOREIGN KEY +
( `CustID` )+
REFERENCES `Customer` +
('Cannot insert - value does not exist in Customer',+
'Cannot update - value does not exist in Customer')
ALTER TABLE `IHTemp` ADD FOREIGN KEY +
( `EmpID` )+
REFERENCES `Employee` +
('Cannot insert - value does not exist in Employee',+
'Cannot update - value does not exist in Employee')
COMMENT ON TABLE `IHTemp` IS +
'Invoice Header Information'
RETURN
--------------------------------------------------
From: "Rachael Malberg" <[email protected]>
Sent: Tuesday, March 16, 2010 10:12 AM
To: "RBASE-L Mailing List" <[email protected]>
Subject: [RBASE-L] - Re: Temp Tables
well if you add it then unloaded the temp tables structure with indexes
then you could parse out the index code as needed.
----- Original Message -----
From: "MDRD" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Tuesday, March 16, 2010 9:37 AM
Subject: [RBASE-L] - Re: Temp Tables
Rachael
No, I didn't think that would make a difference and I have to use the
code anyway,
But it might be fun to try.
Marc
--------------------------------------------------
From: "Rachael Malberg" <[email protected]>
Sent: Tuesday, March 16, 2010 8:45 AM
To: "RBASE-L Mailing List" <[email protected]>
Subject: [RBASE-L] - Re: Temp Tables
have you tried adding it in the table designer?
----- Original Message -----
From: "MDRD" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Tuesday, March 16, 2010 8:19 AM
Subject: [RBASE-L] - Re: Temp Tables
Hi Rachael
I thought that too but the second FK fails. Also, I did Unload the
structure of the Hist table
then change the table name but it still fails when trying to add FK's
on Temp Tables.
Thanks
Marc
--------------------------------------------------
From: "Rachael Malberg" <[email protected]>
Sent: Tuesday, March 16, 2010 7:38 AM
To: "RBASE-L Mailing List" <[email protected]>
Subject: [RBASE-L] - Re: Temp Tables
try adding it in the table designer then unload the table structure
to
get the code. I think you need the 'cannot insert...' messages but
not 100% on this.
It is something like...
ALTER TABLE `tran_spd` ADD FOREIGN KEY +
( `treat_dr` )+
REFERENCES `DRINFO` +
('Cannot insert - value does not exist in DRINFO',+
'Cannot update - value does not exist in DRINFO')
----- Original Message -----
From: "Albert Berry" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Tuesday, March 16, 2010 1:57 AM
Subject: [RBASE-L] - Re: Temp Tables
You must have a primary key on DRINFO before you can apply the FK on
the temp table. I just tested, and I can apply the FK from the table
designer, but get the same error message when I try to create it at
the R> prompt.
MDRD wrote:
Hi
Can you have a FK on a Temp Table?
ALTER TABLE `tran_spd` ADD FOREIGN KEY ( `treat_dr` ) +
REFERENCES `DRINFO`
I keep getting an error "referenced table does not exists.."
Thanks
Marc
