Key Properties of a DB :

1. Data Consistency
2. Data Durability
3. Data Integrity
4. Constraints

Every transaction (or operation) in DBs must follow these properties :

A.C.I.D

A --> Atomicity

C --> Consistency

I --> Isolation

D --> Distributed

Atomicity :

All statements in a transaction takes effect or none take place

Why ??

Say in a DB two transactions have to take place, say in two separate tables and machine crashes before second transaction takes place then our system will be in an inconsistent state

Eg :

Consistency :

Data will never go incorrect, no matter what constraints, cascades, triggers.

• Constraints ⇒ rules enforced by the database system to restrict the type of data that can be stored in tables. They prevent invalid data from being inserted, updated, or deleted.

  • Common Types of Constraints
    • PRIMARY KEY – Uniquely identifies each row.
    • FOREIGN KEY – Enforces relationships between tables.
    • UNIQUE – Ensures all values in a column are different.
    • NOT NULL – Prevents null values.
    • CHECK – Enforces a logical condition.
    • DEFAULT – Assigns a default value if none is provided.

  Eg : Foreign key checks do not allow you to delete parent if child exists

• Cascading ⇒ automatic actions performed on related records when a referenced record is updated or deleted.

  These are mainly used with FOREIGN KEY constraints**.**

  • Types of Cascade Actions :
    • ON DELETE CASCADE
      • When a parent row is deleted, all related child rows are deleted automatically.
    • ON UPDATE CASCADE
      • When a parent key is updated, the corresponding foreign keys in child rows are updated.
    • ON DELETE SET NULL
      • Sets the foreign key in child rows to NULL.
    • ON DELETE SET DEFAULT
      • Sets the foreign key to its default value.
    • ON DELETE RESTRICT / NO ACTION
      • Prevents deletion if related child rows exist.

  CREATE TABLE Orders (
      order_id INT PRIMARY KEY,
      customer_id INT,
      FOREIGN KEY (customer_id)
      REFERENCES Customers(customer_id)
      ON DELETE CASCADE
  );
  
  # If a customer is deleted, all their orders are 
  # automatically deleted, maintaining referential 
  # consistency.
  

• Triggers ⇒ special stored procedure that automatically executes when a specific event occurs on a table

  • Trigger Events :
    • INSERT
    • UPDATE
    • DELETE
  • Trigger Timings :
    • BEFORE : Executes before the operation
    • AFTER : Executes after the operation
    • INSTEAD OF : Replaces the operation

  CREATE TRIGGER prevent_negative_balance
  BEFORE UPDATE ON Accounts
  FOR EACH ROW
  BEGIN
      IF NEW.balance < 0 THEN
          SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Balance cannot be negative';
      END IF;
  END;
  

<aside>

Read More :

More on Consistency

</aside>

Isolation :