Constraints in RDBMS for maintaining consistency
1. Check Constraint: Check Constraint in database ensures that any information before getting inserted adheres to the rules applied to it, there by ensuring the correctness of the data.
Ex : A value of age column must be in some range (greater than 0, less than 60, etc.)
2. Key Constraint: Key constraint generally ensure the uniqueness of the database, and maintain its integrity by preventing the insertion of redundant data. There are different types of key constraints : Primary Key, Foreign Key, Composite key etc.
3. Cascade: Cascade option is associated with the foreign key constraint & it is the mechanism implemented to ensure a specific action (typically update or delete) is taken automatically just in case related records undergo some changes. For example, if there are two entities ‘Post‘ and ‘Comments’ made on that post, then if a post is deleted than all the comments associated with that post must also be deleted from the database, to ensure consistency & avoid redundancy.
4. Trigger: Trigger as its name indicates are nothing but SQL queries that are executed automatically in reaction to specific events or actions typically when new data is inserted, updated or deleted.
5. Atomicity: Any transaction taking place in a database is always going to be FULLY executed or it won’t be executed at all. For example, below scenarios will never take place while making a transaction form one account to another.
- Amount is debited from one account but is never credited to another.
- Amount is credited from one account but was never debited from another.
What is Database Consistency?
Database consistency governs the most crucial aspects of the database, availability of correct information at the right time by the validation rules specified in the beginning is what decides the data integrity, and accuracy of the data stored.
In this article, we’ll be exploring consistency in the context of a relational database, why it’s crucial to be maintained, its real-world examples, and what features of an RDBMS help maintain a consistent database.