Hurray I am happy to have help the list that have helped me so much in the past
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 19:39 Till: RBASE-L Mailing List Ämne: [RBASE-L] - Re: Temp Tables 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 >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
