Activity 7: Data Definition and Constraints

# Access Supabase SQL Editor
# Navigate to your Supabase project dashboard
# Open SQL Editor tab
# Enable required extensions

-- Enable UUID and encryption extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create custom domain types for validation
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}

### First, Create the BAD Example (What NOT to Do)
```sql
-- WARNING: This is intentionally dangerous for demonstration!
CREATE TABLE bad_patients (
    id TEXT,
    ssn TEXT,
    name TEXT,
    birth_date TEXT,
    blood_type TEXT,
    dosage TEXT
);

-- Try inserting chaos:
INSERT INTO bad_patients VALUES 
    (1, '123-45-6789', 'Alice Johnson', '1990-05-15', 'A+', '100mg'),
    (1, '234-56-7890', 'Bob Smith', '1985-12-03', 'O-', '50units'),  -- Duplicate ID!
    (2, 'invalid-ssn', 'Baby Patient', '2030-01-01', 'Z+', '-50mg'); -- Future birth, invalid blood type!

-- Everything gets inserted! This is why we need constraints.

CREATE TABLE patients (
    patient_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    medical_record_number VARCHAR(20) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Benefits: Guaranteed uniqueness, no integer overflow, distributed-safe

CREATE TABLE healthcare_providers (
    provider_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    license_number VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

CREATE TABLE treatments (
    treatment_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    patient_id UUID NOT NULL REFERENCES patients(patient_id) ON DELETE RESTRICT,
    provider_id UUID NOT NULL REFERENCES healthcare_providers(provider_id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ON DELETE RESTRICT prevents orphaned records
-- ON DELETE CASCADE automatically cleans up related data

-- Add comprehensive validation to patients table
ALTER TABLE patients ADD COLUMN 
    first_name VARCHAR(50) NOT NULL CHECK (LENGTH(first_name) >= 2),
    last_name VARCHAR(50) NOT NULL CHECK (LENGTH(last_name) >= 2),
    birth_date DATE NOT NULL CHECK (
        birth_date <= CURRENT_DATE AND birth_date >= '1900-01-01'
    ),
    age INT GENERATED ALWAYS AS (DATE_PART('year', AGE(birth_date))::INT) STORED,
    blood_type blood_type NOT NULL,
    email email_address,
    phone phone_number,
    
    -- Complex constraint combining multiple fields
    CONSTRAINT valid_age CHECK (age >= 0 AND age <= 150),
    CONSTRAINT adult_emergency_contact CHECK (
        age  &lt;  18 OR emergency_contact_name IS NOT NULL
    );

CREATE TABLE patient_allergies (
    patient_id UUID,
    allergen VARCHAR(100),
    severity VARCHAR(20) CHECK (severity IN ('Mild', 'Moderate', 'Severe', 'Life-threatening')),
    discovered_date DATE,
    PRIMARY KEY (patient_id, allergen),  -- Composite primary key
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE
);

-- Prevents duplicate allergy records for same patient

-- Medication dosage validation
CREATE DOMAIN dosage_amount AS DECIMAL(10,4) 
    CHECK (VALUE > 0 AND VALUE <= 10000);

CREATE TABLE medications (
    medication_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    generic_name VARCHAR(200) NOT NULL,
    min_dosage dosage_amount,
    max_dosage dosage_amount,
    CONSTRAINT dosage_range CHECK (max_dosage >= min_dosage)
);

-- Insert a valid patient first
INSERT INTO patients (medical_record_number, first_name, last_name, birth_date, blood_type)
VALUES ('MRN001', 'Alice', 'Johnson', '1990-05-15', 'A+');

-- Try to break unique constraint
INSERT INTO patients (medical_record_number, first_name, last_name, birth_date, blood_type)
VALUES ('MRN001', 'Bob', 'Smith', '1985-12-03', 'O-');
-- Result: ERROR - duplicate key value violates unique constraint

-- Try impossible birth dates
INSERT INTO patients (medical_record_number, first_name, last_name, birth_date, blood_type)
VALUES ('MRN002', 'Future', 'Baby', '2030-01-01', 'O+');
-- Result: ERROR - birth_date > CURRENT_DATE

-- Try invalid blood type
INSERT INTO patients (medical_record_number, first_name, last_name, birth_date, blood_type)
VALUES ('MRN003', 'John', 'Doe', '1990-01-01', 'Z+');
-- Result: ERROR - domain constraint violation

-- Try short names
INSERT INTO patients (medical_record_number, first_name, last_name, birth_date, blood_type)
VALUES ('MRN004', 'X', 'Y', '1990-01-01', 'A+');
-- Result: ERROR - check constraint violation on name length

-- Try treatment with non-existent patient
INSERT INTO treatments (patient_id, provider_id)
VALUES ('00000000-0000-0000-0000-000000000000', 
        (SELECT provider_id FROM healthcare_providers LIMIT 1));
-- Result: ERROR - foreign key constraint violation

-- Try to delete patient with treatments (RESTRICT)
DELETE FROM patients WHERE medical_record_number = 'MRN001';
-- Result: ERROR - foreign key constraint prevents deletion

-- Try duplicate allergy for same patient
INSERT INTO patient_allergies VALUES 
    ((SELECT patient_id FROM patients LIMIT 1), 'Penicillin', 'Severe', '2020-01-01');
    
INSERT INTO patient_allergies VALUES 
    ((SELECT patient_id FROM patients LIMIT 1), 'Penicillin', 'Mild', '2021-01-01');
-- Result: ERROR - primary key constraint violation on composite key

-- 1. Verify no bad data exists
SELECT COUNT(*) as invalid_ages 
FROM patients 
WHERE age  <  0 OR age > 150;

-- 2. Check constraint enforcement
SELECT conname, confdeltype, confupdtype 
FROM pg_constraint 
WHERE conrelid = 'patients'::regclass;

-- 3. Test referential integrity
SELECT COUNT(*) as orphaned_treatments
FROM treatments t
LEFT JOIN patients p ON t.patient_id = p.patient_id
WHERE p.patient_id IS NULL;

### First, Create the BAD Example (What NOT to Do)
__CODE_BLOCK_2__

## 🔍 Core Concept: Production Constraint Types (100 lines)

### 1. Primary Key Constraints (UUID Best Practice)
__CODE_BLOCK_3__

### 2. Foreign Key Constraints with Actions
__CODE_BLOCK_4__

### 3. Check Constraints (Business Logic Validation)
__CODE_BLOCK_5__

### 4. Unique Constraints and Composite Keys
__CODE_BLOCK_6__

### 5. Domain Constraints (Custom Data Types)
__CODE_BLOCK_7__

## 💻 Hands-On Practice: Break the Constraints (100 lines)

### Test Suite 1: Primary Key Violations
__CODE_BLOCK_8__

### Test Suite 2: Check Constraint Violations
__CODE_BLOCK_9__

### Test Suite 3: Foreign Key Violations
__CODE_BLOCK_10__

### Test Suite 4: Complex Business Logic
__CODE_BLOCK_11__

## ✅ Verify Results (25 lines)

### Constraint Testing Checklist
__CODE_BLOCK_12__

**✅ Success Criteria:**
- All constraint violations properly rejected
- No invalid data in any table
- Foreign key relationships maintained
- Composite keys prevent logical duplicates
- Domain constraints enforce data quality

## 📚 Deep Dive: Production Constraint Strategy (50 lines)

### Constraint Types and Use Cases
**Primary Keys**: Guarantee entity uniqueness
- Use UUIDs for distributed systems
- Auto-incrementing integers for single-server apps

**Foreign Keys**: Maintain referential integrity
- RESTRICT: Prevent orphaned records
- CASCADE: Automatic cleanup
- SET NULL: Soft delete scenarios

**Check Constraints**: Enforce business rules
- Range validation (ages, dates, amounts)
- Enum-like restrictions (status values)
- Cross-field validation

**Unique Constraints**: Prevent logical duplicates
- Single column: unique email addresses
- Composite: prevent duplicate combinations

**Not Null Constraints**: Enforce required fields
- Critical for system integrity
- Consider defaults vs requirements

### Performance Considerations
**Indexing Impact**: Constraints automatically create indexes
- Primary key: Clustered index created
- Unique constraints: Unique index created
- Foreign keys: Consider indexing referenced columns

**Validation Cost**: Complex constraints impact INSERT/UPDATE
- Simple checks: Minimal overhead
- Subquery checks: Significant performance cost
- Trigger-based validation: Most expensive

### Healthcare Compliance
**HIPAA Requirements**: 
- Audit trails for all data changes
- Encryption of sensitive fields (SSN, etc.)
- Access logging and constraint violations

**FDA Compliance**:
- Complete data validation for clinical trials
- Immutable audit records
- Zero tolerance for data corruption

**Career Applications**:
- Healthcare systems engineering ($120K-180K)
- Financial compliance systems ($130K-200K)
- Government security clearance roles ($140K-220K)
- Enterprise architecture leadership ($180K-300K)

## 📤 Submit Your Work

Complete this activity and submit your work through the [Activity Submission Form](https://forms.gle/JWJS5CwTqb3YX8776)