Wednesday, July 13, 2011 Tip of the Day: Understanding Referential Data Integrity Product: R:BASE (True-Relational Database Management System) Section: Referential Data Integrity
Did you know that PRIMARY and FOREIGN KEYs automatically preserve referential data integrity? Once PRIMARY and FOREIGN KEYs are defined accordingly, R:BASE automatically preserves the referential data integrity. . You cannot delete a row form a table with defined PRIMARY KEY if there are referenced FOREIGN KEYs. Therefore, you can never have detail records without a matching master record. . You can DELETE a row from a table with a FOREIGN KEY. Y . You cannot add a row to table with FOREIGN KEY defined unless the value entered matches a value in the referenced PRIMARY KEY. . You cannot UPDATE a PRIMARY KEY value if there are references, thus you are ensured that linking columns always match. . You can UPDATE PRIMARY KEY values, provided there are not matching values in the referenced FOREIGN KEY. . You may use the CASCADE option on PRIMARY KEYs. When a PRIMARY KEY is defined as a CASCADING PRIMARY KEY, referenced FOREIGN KEY values are automatically UPDATEd or DELETEd. This allows you to update or delete referenced PRIMARY KEY values and retain the referential data integrity. . The latest and greatest versions of R:BASE eXtreme 9.1 (32/64) "Data Designer" provides you with such option without using the ALTER TABLE and CREATE TABLE commands at the R> prompt. http://www.rupdates.com . CASCADE Maintains PRIMARY/FOREIGN key relationships automatically. A CASCADE can be applied to UPDATE, DELETE, or BOTH data set changes for primary keys. For example, if you either UPDATE or DELETE a PRIMARY KEY value from a table, the corresponding FOREIGN KEY values are updated or deleted automatically. . CASCADE can only be added to tables with PRIMARY KEYs. . UPDATE - enforces that when a primary key value is updated, the corresponding value in the foreign key table(s) will also be updated. . DELETE - enforces that when a primary key value is deleted, the corresponding value in the foreign key table(s) will also be deleted. . Separate UPDATE and DELETE data restrictions can allow a CASCADE to be enforced for values that are updated, but not enforced when records are deleted, in order to avoid an accidental/undesired record delete. Very Best R:egards, Razzak
