Designing Tables in Relational Database Systems

Apple has unveiled iPhone 6, a larger handset of 4.7 inches in gold and dark black colours and the iPhone 6 plus, recently. It also has unveiled a rectangular wrist watch and ‘Apple pay’ after two years. The watch is a comprehensive health and fitness device, synched to the internet and precise to 50 milliseconds said the company. It also allows users to dictate messages via a microphone. There are two sizes of the watch, dubbed as male and female versions.

Now to referential integrity.

The Transaction table (refer May article for the table definitions) has columns as shown

TRANSACTION

CUSTOMERSWhen a record of  a transaction is entered the row should have a valid Customer_no in the customers table .The CUSTOMERS table’s structure is as below which we have seen in the previous article.

How to ensure that such a logical violation does not happen and that data integrity is maintained? The relationship between CUSTOMERS table and TRANSACTION table is one too many. HereThe system should not allow a transaction without a valid customer record in the CUSTOMERS table to be entered in the TRANSACTION table. Otherwise, the solution is to create  the customer record in the CUSTOMERS table and then the transaction gets entered.

Customer_no the foreign key in TRANSACTIONS should have a corresponding primary key in the CUSTOMERS table. (Refer May article)  For one customer there may be many rows in the transaction table according to  the transactions she makes. For every transaction row in the TRANSACTION table there should exist a corresponding row in CUSTOMERS that identifies the customer. In Oracle the ‘References Constraint’ is used to ensure that a valid customer exists before the dependent row is added in the TRANSACTIONS table. In Oracle the transaction* of inserting the record in TRANSACTION table is rolled back if the customer did not exist in the CUSTOMERS table. Rolling back means the transaction does not happen (or is not committed).

*Here the activity of entering the row in the table is referred to as a transaction. The transaction getting committed means the activity was successful and rolling back the transaction means it was not successful.

Therefore the logical relationship between the two tables is ensured or maintained using this constraint. This concept is called Referential Integrity and is critical to the building of logical relationships between tables and ensuring that they are maintained in a relational database system.

Suppose a customer record is deleted in the CUSTOMERS table then all the corresponding transactions of the customer should be removed from the TRANSACTION table. It is strenuous to remove them manually. Again the referential integrity concept is applied and a Casacading Delete is done. This means the corresponding rows of that customer in the TRANSACTION table get deleted automatically when such an event happens. The same constraint helps to implement cascading effects in updation of records. This means that if a primary key value, that is customer number changes in the CUSTOMERS table, the corresponding records in the TRANSACTION table should reflect that. Hence, Referential integrity ensures logical consistency in the relationship between the tables in insert, update and delete operations.

The syntax to create the constraint is as below

ALTER TABLE transactions

ADD

CONSTRAINT (cust_no_fk) FOREIGN KEY (customer_no) REFERENCES customers(customer_no); the statement means that we are adding a referential integrity constraint called cust_no_fk to the transactions table. It refers to the primary key in customers table to ensure data integrity.

Referential integrity is an important concept in relational databases and enforces business rules and ensures data integrity. The designers of the tables should be well aware of this concept and implementation.