[RBASE-L] - RE: Tip of the Day: Using FOREIGN KEYS in TEMPORARYTables

[Stephen Markson]

Hello Razzak,

Thank you, this is useful for us here at PEBC. I've tested this and, once 
again, RBTI has risen to the referential integrity challenges presented by this 
change. Specifically:

1) any FKs referencing a temp table PK are dropped when the temp table is 
deleted (either dropped or DB disconnected).
 
2) any temp table FK references to a permanent table PK are removed when the 
temp table is deleted. (i.e. the perm table PK is no longer REFERENCED if there 
are no other tables referencing the permanent table PK).

Well done!


Regards,

Stephen Markson
The Pharmacy Examining Board of Canada
416.979.2431 x251

-----Original Message-----
From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On Behalf Of A. Razzak Memon
Sent: January-31-14 12:00 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Tip of the Day: Using FOREIGN KEYS in TEMPORARY Tables

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