*Sniff* It's a beautiful thing you've done ... Bruce Chitiea 909.238.9012 cell
> On Jan 30, 2014, at 21:00, "A. Razzak Memon" <[email protected]> wrote: > > Friday, January 31, 2014 > > Tip of the Day: Using Foreign Keys in TEMPORARY Tables > Product.: R:BASE eXtreme 9.5 (32/64) > Build...: 9.5.4.30130 or higher www.rupdates.com > Keywords: Data Integrity, Constraints > Section.: TEMPORARY Tables/Views > > Since the introduction of TEMPORARY Tables and Views in July 1997 > (R:BASE 6.1), and the constant innovations in the latest versions, > the use of TEMPORARY tables & views has become the most valuable > feature in R:BASE eXtreme 9.5. > > While using TEMPORARY tables and collecting data, the referential > integrity becomes a challenge. > > Did you know that now you can define a FOREIGN KEY on a TEMPORARY > table having REFERENCES to a permanent table? > > The latest version and build of R:BASE eXtreme 9.5 (32/64) now > supports the use of FOREIGN KEY (FK) on a TEMPORARY table with > REFERENCES to a permanent table. > > R:BASE Data Designer (RBDEFine) also supports the ability to add > FOREIGN KEY constraints to TEMPORARY tables. > > Here is sample code that defines TEMPORARY Tables with PRIMARY > and FOREIGN KEYS (based on RRBYW18 sample database). > > -- Example 01 > -- Define TEMPORARY tInvoiceHeader Table > -- January 31, 2014 > SET ERROR MESSAGE 2038 OFF > DROP TABLE tInvoiceHeader > SET ERROR MESSAGE 2038 ON > CREATE TEMPORARY TABLE `tInvoiceHeader` + > (`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 `tInvoiceHeader` ADD PRIMARY KEY + > (`TransID`) + > ('Values for rows in tInvoiceHeader must be Unique',+ > 'Cannot Delete - values exist in another table',+ > 'Cannot Update - values exist in another table') > ALTER TABLE `tInvoiceHeader` ADD FOREIGN KEY + > (`CustID`)+ > REFERENCES `Customer` + > ('Cannot Insert - value does not exist in Customer',+ > 'Cannot Update - value does not exist in Customer') > COMMENT ON TABLE `tInvoiceHeader` IS + > 'TEMPORARY Table for Invoice Header' > RETURN > > -- Example 02 > -- Define TEMPORARY tInvoiceDetail Table > -- January 31, 2014 > SET ERROR MESSAGE 2038 OFF > DROP TABLE tInvoiceDetail > SET ERROR MESSAGE 2038 ON > CREATE TEMPORARY TABLE `tInvoiceDetail` + > (`TransID` INTEGER, + > `DetailNum` INTEGER, + > `Model` TEXT (6), + > `Units` INTEGER, + > `Price` CURRENCY, + > `Discount` REAL, + > `SalePrice`= + > (Price- (Price*Discount/100)) CURRENCY, + > `ExtPrice`= (Units*SalePrice) CURRENCY) > ALTER TABLE `tInvoiceDetail` ADD FOREIGN KEY + > (`TransID`)+ > REFERENCES `tInvoiceHeader` + > ('Cannot Insert - value does not exist in tInvoiceHeader',+ > 'Cannot Update - value does not exist in tInvoiceHeader') > COMMENT ON TABLE `tInvoiceDetail` IS + > 'TEMPORARY Invoice Detail Table' > RETURN > > Have fun using TEMPORARY tables in R:BASE eXtreme 9.5 (32/64)! > > Very Best R:egards, > > Razzak. > > www.rbase.com > www.facebook.com/rbase > www.Razzak.com > www.RazzakMemon.com > -- > 31 years of continuous innovation! > 16 Years of R:BASE Technologies, Inc. making R:BASE what it is today! > -- > >
