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