4.3Testing Each Constraint Type

Primary Key

def test_primary_key_prevents_duplicates(db):
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO users (id, name, email) VALUES (%s, %s, %s)",
        ("user-001", "First", "first@test.com")
    )
    with pytest.raises(psycopg2.errors.UniqueViolation):
        cursor.execute(
            "INSERT INTO users (id, name, email) VALUES (%s, %s, %s)",
            ("user-001", "Duplicate", "duplicate@test.com")
        )

Foreign Key

def test_foreign_key_prevents_orphans(db):
    """Cannot create an order for a non-existent user."""
    cursor = db.cursor()
    with pytest.raises(psycopg2.errors.ForeignKeyViolation):
        cursor.execute(
            "INSERT INTO orders (user_id, total) VALUES (%s, %s)",
            ("nonexistent-user-id", 99.99)
        )

def test_foreign_key_cascade_delete(db):
    """When a user is deleted, their orders should also be deleted (if CASCADE)."""
    cursor = db.cursor()
    # Create user and order
    cursor.execute("INSERT INTO users (id, name, email) VALUES (%s, %s, %s)",
                   ("cascade-test", "Cascade User", "cascade@test.com"))
    cursor.execute("INSERT INTO orders (id, user_id, total) VALUES (%s, %s, %s)",
                   ("cascade-order", "cascade-test", 50.00))

    # Delete user
    cursor.execute("DELETE FROM users WHERE id = %s", ("cascade-test",))

    # Verify order was also deleted (if CASCADE is set)
    cursor.execute("SELECT COUNT(*) FROM orders WHERE id = %s", ("cascade-order",))
    assert cursor.fetchone()[0] == 0, "Order should be cascade-deleted with user"

def test_foreign_key_restrict_delete(db):
    """If RESTRICT: deleting a user with orders should fail."""
    cursor = db.cursor()
    cursor.execute("INSERT INTO users (id, name, email) VALUES (%s, %s, %s)",
                   ("restrict-test", "Restrict User", "restrict@test.com"))
    cursor.execute("INSERT INTO orders (id, user_id, total) VALUES (%s, %s, %s)",
                   ("restrict-order", "restrict-test", 50.00))

    with pytest.raises(psycopg2.errors.ForeignKeyViolation):
        cursor.execute("DELETE FROM users WHERE id = %s", ("restrict-test",))

Unique Constraint

def test_unique_email_constraint(db):
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (%s, %s)",
        ("User A", "dup@test.com")
    )
    with pytest.raises(psycopg2.errors.UniqueViolation):
        cursor.execute(
            "INSERT INTO users (name, email) VALUES (%s, %s)",
            ("User B", "dup@test.com")
        )

def test_unique_constraint_case_sensitivity(db):
    """Test whether the unique constraint is case-sensitive."""
    cursor = db.cursor()
    cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)",
                   ("User A", "test@example.com"))
    try:
        cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)",
                       ("User B", "TEST@EXAMPLE.COM"))
        # If this succeeds, the constraint is case-sensitive
        # (this may or may not be desired behavior)
    except psycopg2.errors.UniqueViolation:
        # Constraint is case-insensitive — usually the desired behavior for email
        pass

NOT NULL Constraint

def test_not_null_email(db):
    cursor = db.cursor()
    with pytest.raises(psycopg2.errors.NotNullViolation):
        cursor.execute(
            "INSERT INTO users (name, email) VALUES (%s, %s)",
            ("No Email User", None)
        )

def test_not_null_name(db):
    cursor = db.cursor()
    with pytest.raises(psycopg2.errors.NotNullViolation):
        cursor.execute(
            "INSERT INTO users (name, email) VALUES (%s, %s)",
            (None, "valid@test.com")
        )

CHECK Constraint

def test_check_price_non_negative(db):
    """Price must be >= 0."""
    cursor = db.cursor()
    with pytest.raises(psycopg2.errors.CheckViolation):
        cursor.execute(
            "INSERT INTO products (name, price) VALUES (%s, %s)",
            ("Bad Product", -10.00)
        )

def test_check_allows_zero_price(db):
    """Zero price should be allowed (free products)."""
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO products (name, price) VALUES (%s, %s)",
        ("Free Product", 0.00)
    )
    # Should succeed without error

def test_check_status_enum(db):
    """Status must be one of the allowed values."""
    cursor = db.cursor()
    with pytest.raises(psycopg2.errors.CheckViolation):
        cursor.execute(
            "INSERT INTO orders (user_id, status, total) VALUES (%s, %s, %s)",
            ("user-001", "invalid_status", 50.00)
        )

Default Values

def test_default_role_applied(db):
    """When role is not specified, default should be 'viewer'."""
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING role",
        ("Default Role User", "default@test.com")
    )
    role = cursor.fetchone()[0]
    assert role == "viewer"

def test_default_timestamp_applied(db):
    """created_at should default to current time."""
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING created_at",
        ("Timestamp User", "timestamp@test.com")
    )
    created_at = cursor.fetchone()[0]
    assert created_at is not None
    # Should be within the last few seconds
    from datetime import datetime, timedelta
    assert datetime.now() - created_at < timedelta(seconds=5)