Learn Consistency

SQL Optimization and Query Features

Swipe to show menu

Consistency is a fundamental property in SQL databases, ensuring that every transaction moves the database from one valid state to another. This means that all data must always satisfy the rules and constraints defined for the database. Constraints are rules you set on tables and columns to enforce valid data. Common constraints include NOT NULL (which ensures that a column cannot have missing values); CHECK (which enforces that values meet a specific condition); and FOREIGN KEY (which maintains relationships between tables). In the context of the bank_accounts table, constraints help ensure that every account always has a valid owner name and a valid balance. For example, the NOT NULL constraint on owner_name and balance ensures that no account can exist without an owner or a balance value. If your database allows negative balances, it could lead to invalid states, such as an account owing money when that is not allowed by business rules.


              12345678910
            
-- Drop the CHECK constraint if it already exists to avoid errors
ALTER TABLE bank_accounts
DROP CONSTRAINT IF EXISTS balance_nonnegative;

-- Add a CHECK constraint to ensure balances are never negative
ALTER TABLE bank_accounts
ADD CONSTRAINT balance_nonnegative CHECK (balance >= 0);

-- Show current balances in the bank_accounts table
SELECT account_id, owner_name, balance FROM bank_accounts;

The CHECK constraint added above enforces that the balance in the bank_accounts table can never be less than zero. If you attempt to insert or update a row with a negative balance, the database will reject the operation and return an error. This prevents accidental or malicious changes that would violate your business rules and ensures the database remains in a consistent state. If a transaction tries to set an account's balance to a negative value, the transaction will fail at the point of violation and will not be committed, keeping the data valid.

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 4

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 1. Chapter 4

Consistency

1. What is the role of constraints in maintaining consistency?

2. What happens if a transaction violates a CHECK constraint?

3. Can a transaction be committed if it leaves the database in an inconsistent state?