Constraint Testing

Database constraints are the database's built-in test suite. They enforce data integrity rules at the lowest level — even if the application code has a bug, constraints prevent invalid data from being stored. Testing constraints verifies that the safety net is in place.

Types of Constraints

Testing 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)

Why Test Constraints?

"If the database has the constraint, why test it?" Because:

1. Constraints can be accidentally removed during migrations
2. Constraints may not exist even when you assume they do
3. Application code may bypass constraints (e.g., ORM bug, raw SQL)
4. Constraint behavior varies (CASCADE vs RESTRICT, case sensitivity)
5. Documentation — constraint tests document the data integrity rules

Discovering Existing Constraints

-- List all constraints on a table (PostgreSQL)
SELECT con.conname, con.contype, pg_get_constraintdef(con.oid)
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
WHERE rel.relname = 'users';

-- contype: p = primary key, f = foreign key, u = unique, c = check

Practical Exercise

1. Write tests for each constraint type on a users table: PK, FK, UNIQUE, NOT NULL, CHECK
2. Test CASCADE vs RESTRICT behavior on a foreign key
3. Test default values: verify defaults are applied when columns are omitted
4. Query the database to discover all constraints on a table, then write tests for each
5. Test a case-sensitivity edge case on a unique constraint

Key Takeaways

• Database constraints are the last line of defense for data integrity
• Test every constraint type: PK, FK, UNIQUE, NOT NULL, CHECK, DEFAULT
• Test both the positive case (valid data accepted) and negative case (invalid data rejected)
• Foreign key behavior (CASCADE, RESTRICT, SET NULL) must be tested explicitly
• Constraints can be accidentally removed during migrations — tests catch this