Referential Integrity Explained: Bonding Tables Together - How is Referential Integrity Maintained?
(Page 3 of 4 )
A long, long time ago, before the database world had really powerful products, referential integrity was programmed in each application SW piece. Image all the possible sources for bugs popping up all over the place!
A relational database system supports the enforcement of referential integrity as constraints: special definitions done by the DDL (Data Definition Language) part of the database. Let us look at how these constraints can be defined by using Oracle's DDL (I have the full script also for SQL Server in the Database Normalization eBook). The constraint definitions look like this:
The first two ALTER-statements are establishing the primary key for each table. The third statement is the important one in our context: it tells Oracle that the ACCOUNT_NO in TRANSACTIONS is referencing the primary key ACCOUNT_NO in ACCOUNTS, and that Oracle should see to it that any references from TRANSACTIONS to a given account in ACCOUNTS should never be broken. And, if you delete a row in ACCOUNTS that has TRANSACTIONS, then that's exactly what you are trying to do!
Actually, the last constraint definition also establishes ACCOUNT_NO in TRANSACTIONS as a foreign key for ACCOUNTS. That is really most of the difference between primary and foreign keys: a primary key in one table becomes a foreign key in tables it has a mother-child relation to. But that's a different article for some day to come...
Next: Taking This a Bit Further >>
More Database Articles
More By Alf Pedersen
