At 10:38 AM 1/31/2014, Stephen Markson wrote:
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!
Stephen,
Thank you for your public testimony of R:BASE eXtreme 9.5 - Update 4.
IF anyone is still using R:BASE and their business depends on R:BASE
and R:BASE
ONLY, this feature along with so many engine-level fixes & enhancements should
be the prime reason to migrate all legacy versions to R:BASE eXtreme 9.5.
We will continue to do our best and help those with vested interest in R:BASE!
Nobody takes these matters more seriously than the developer and user of our
own product.
Very Best R:egards,
Razzak
-----Original Message-----
From: [email protected] [mailto:[email protected]] 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!
--
