Skip to content

8.transactions_constraints.md

Latest commit

 

History

History
292 lines (233 loc) · 6.45 KB

8.transactions_constraints.md

File metadata and controls

292 lines (233 loc) · 6.45 KB

SQL Transactions and Constraints

This lesson covers two crucial aspects of database management: transactions for maintaining data integrity and constraints for enforcing data rules.

Transactions

Transactions ensure that a series of SQL operations are executed as a single unit of work, following the ACID properties (Atomicity, Consistency, Isolation, Durability).

Basic Transaction Commands

-- Start a transaction
BEGIN;

-- Perform operations
UPDATE Books
SET CurrentStock = CurrentStock - 1
WHERE BookID = 1;

INSERT INTO Loans (MemberID, BookID, LoanDate)
VALUES (101, 1, GETDATE());

-- If everything is OK, commit the transaction
COMMIT;

-- If there's an error, rollback the transaction
-- ROLLBACK;

Practical Transaction Examples

-- Example 1: Book Return Process
BEGIN;
TRY
    -- Update the loan record
    UPDATE Loans
    SET ReturnDate = GETDATE()
    WHERE LoanID = 123;

    -- Increase book stock
    UPDATE Books
    SET CurrentStock = CurrentStock + 1
    WHERE BookID = (SELECT BookID FROM Loans WHERE LoanID = 123);

    COMMIT;
CATCH
    ROLLBACK;
    THROW;
END;

-- Example 2: Member Registration with Initial Loan
BEGIN;
TRY
    -- Insert new member
    INSERT INTO Members (FirstName, LastName, Email)
    VALUES ('John', 'Doe', 'john@example.com');

    -- Get the new member's ID
    DECLARE @NewMemberID INT = SCOPE_IDENTITY();

    -- Create their first loan
    INSERT INTO Loans (MemberID, BookID, LoanDate)
    VALUES (@NewMemberID, 1, GETDATE());

    -- Update book stock
    UPDATE Books
    SET CurrentStock = CurrentStock - 1
    WHERE BookID = 1;

    COMMIT;
CATCH
    ROLLBACK;
    THROW;
END;

Constraints

Constraints are rules enforced on database columns to maintain data integrity.

Types of Constraints

1. NOT NULL

Ensures a column cannot have NULL value.

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    ISBN VARCHAR(13) NOT NULL
);

2. UNIQUE

Ensures all values in a column are different.

CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    LibraryCardNumber VARCHAR(20) UNIQUE
);

3. CHECK

Ensures all values in a column satisfy certain conditions.

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) CHECK (Price >= 0),
    Rating INT CHECK (Rating BETWEEN 1 AND 5)
);

-- Multiple conditions in CHECK constraint
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    LoanDate DATE,
    ReturnDate DATE,
    CONSTRAINT ValidDates CHECK (ReturnDate IS NULL OR ReturnDate >= LoanDate)
);

4. DEFAULT

Sets a default value for a column when no value is specified.

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    PublicationDate DATE DEFAULT GETDATE(),
    CurrentStock INT DEFAULT 0,
    IsActive BIT DEFAULT 1
);

5. FOREIGN KEY

Ensures referential integrity between tables.

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(50) NOT NULL
);

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    CategoryID INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

-- With custom actions on update/delete
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    MemberID INT,
    BookID INT,
    FOREIGN KEY (MemberID) 
        REFERENCES Members(MemberID) 
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
    FOREIGN KEY (BookID) 
        REFERENCES Books(BookID) 
        ON DELETE NO ACTION
);

Combining Constraints

-- Example of a comprehensive table with multiple constraints
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    ISBN VARCHAR(13) UNIQUE NOT NULL,
    PublicationDate DATE DEFAULT GETDATE(),
    Price DECIMAL(10,2) CHECK (Price >= 0) DEFAULT 0.00,
    CurrentStock INT CHECK (CurrentStock >= 0) DEFAULT 0,
    CategoryID INT,
    Rating DECIMAL(3,1) CHECK (Rating BETWEEN 0 AND 5) DEFAULT 0,
    IsActive BIT DEFAULT 1,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

Exercise 🔴

-- Create sample tables with appropriate constraints
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL UNIQUE,
    Location VARCHAR(50) DEFAULT 'Main Office'
);

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Salary DECIMAL(10,2) CHECK (Salary >= 0),
    DeptID INT,
    HireDate DATE DEFAULT GETDATE(),
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

-- Tasks:
-- 1. Write a transaction that transfers an employee to a new department
--    and updates related records

-- 2. Add a constraint to ensure employee's email follows company domain

-- 3. Create a transaction that gives a salary raise to all employees
--    in a department, but rollback if any salary exceeds a maximum value

-- Solutions:

-- 1. Employee Transfer Transaction
BEGIN;
TRY
    DECLARE @EmpID INT = 101;
    DECLARE @NewDeptID INT = 2;
    DECLARE @OldDeptID INT;

    -- Get current department
    SELECT @OldDeptID = DeptID 
    FROM Employees 
    WHERE EmpID = @EmpID;

    -- Update employee's department
    UPDATE Employees
    SET DeptID = @NewDeptID
    WHERE EmpID = @EmpID;

    -- Log the transfer
    INSERT INTO TransferLog (EmpID, OldDeptID, NewDeptID, TransferDate)
    VALUES (@EmpID, @OldDeptID, @NewDeptID, GETDATE());

    COMMIT;
CATCH
    ROLLBACK;
    THROW;
END;

-- 2. Email Domain Constraint
ALTER TABLE Employees
ADD CONSTRAINT ValidEmail 
    CHECK (Email LIKE '%@company.com');

-- 3. Department Salary Raise Transaction
BEGIN;
TRY
    DECLARE @DeptID INT = 1;
    DECLARE @RaisePercent DECIMAL(5,2) = 10.0;
    DECLARE @MaxSalary DECIMAL(10,2) = 100000.00;

    -- Apply raise
    UPDATE Employees
    SET Salary = Salary * (1 + @RaisePercent/100)
    WHERE DeptID = @DeptID;

    -- Check if any salary exceeds maximum
    IF EXISTS (
        SELECT 1 
        FROM Employees 
        WHERE DeptID = @DeptID 
        AND Salary > @MaxSalary
    )
    BEGIN
        ROLLBACK;
        THROW 50000, 'Salary raise would exceed maximum allowed salary', 1;
    END

    COMMIT;
CATCH
    ROLLBACK;
    THROW;
END;

This exercise demonstrates:

  • Transaction management with error handling
  • Adding custom constraints
  • Complex business logic with rollback conditions
  • Combining multiple SQL concepts in practical scenarios