Foreign and unique keys are two components of enterprise web applications that help improve data integrity and define relationships between elements of the system. A well-built enterprise app will enforce the keys at both the database and application levels, providing business logic and validation at each system entryway.
For a quick overview – foreign keys are references to other tables in the system. When a database architect splits the system’s data into tables, each table has a primary key that provides a unique ID for each row in that table. For instance, a customer record in the Customer table will have a Customer ID as the primary key. If another table needs to reference a customer, for instance an Order table, it will have a column for the Customer ID. By implementing the Customer ID column as a foreign key, the database will automatically make sure that each Customer ID field exists before adding it to the order. In addition, the database will automatically prevent the user from deleting a Customer record if it is referenced by an Order.
Unique keys are similar to foreign keys, in that they provide additional logic and preventative measures against data corruption. Unique keys simply require that each row in a table has a unique value for that particular column. For instance, a unique key might be set on the Customer Email, so that duplicate customer records are prevented. If a user tries to enter the same email for another Customer, the database will generate an error.
Good web frameworks will implement foreign and unique keys at either two or three tiers, depending on the system structure. Both foreign and unique keys should always be implemented both at the database level, and with friendly error messages on the client side. In addition to these, if the web framework is built using AJAX and web services, there is an additional entry point for each call. Although it is hidden from most users, the AJAX web services are still publicly accessible and can be integrated with other applications. A good web framework will implement additional validation checks on the web service to enable integration with other systems and prevent security loopholes.
Both ASP.NET and PHP have minimal support for automation of foreign key relationships and unique key constraints. ASP.NET has a cludgy system that duplicates the database schema with XSD, however this can be problematic in early-stage development where the database often changes.
Ruby on Rails ignores foreign keys entirely in its automatic table generation, and relies for implementation on the application side. This can be a problem for more serious applications that may have multiple database consumers, and can lead to data corruption. Django, on the other hand, can support simple implementation of foreign keys on some databases.
Most enterprise-grade applications will need to implement foreign keys and unique keys through custom code. Join us in the next article in this series, where we investigate error messages and error logging.
Written by Andrew Palczewski
About the Author
Andrew Palczewski is CEO of apHarmony, a Chicago software development company. He holds a Master's degree in Computer Engineering from the University of Illinois at Urbana-Champaign and has over ten years' experience in managing development of software projects.
Google+
