[BUG] Generated PostgreSQL queries use subqueries in Check constraints

Preflight Checklist

• [x] I have searched existing issues and this hasn't been reported yet
• [x] This is a single bug report (please file separate reports for different bugs)
• [x] I am using the latest version of Claude Code

What's Wrong?

Title: Claude Code generates invalid PostgreSQL CHECK constraints with subqueries

Labels: bug, code-generation, postgresql

Description:

Summary

Claude Code generates PostgreSQL CHECK constraints that include subqueries, which are not supported by PostgreSQL and cause database deployment failures.

Environment

• Claude Code version: [latest as of 2025-10-28]
• Database: PostgreSQL
• Project type: .NET 8 with Dapper ORM

Problem Description

When generating database table creation scripts, Claude Code creates CHECK constraints that contain SELECT subqueries. PostgreSQL fundamentally does not support
subqueries in CHECK constraints, causing these scripts to fail during deployment.

Example of Generated Code

-- ❌ INVALID - Generated by Claude Code
CREATE TABLE utmembershipproduct (
id uuid PRIMARY KEY,
feeterm uuid NOT NULL,
membershipproductoptions uuid[],

CONSTRAINT chk_available_months CHECK (
availablemonths IS NULL OR
(SELECT bool_and(m >= 1 AND m <= 12) FROM unnest(availablemonths) m)
),
CONSTRAINT chk_no_bonus_with_lifetime CHECK (
NOT (
feeterm IN (SELECT id FROM utvalcode WHERE valtype = (...) AND valcode = 'LIFE')
AND membershipproductoptions IS NOT NULL
AND (SELECT id FROM utvalcode WHERE ...) = ANY(membershipproductoptions)
)
)
);

Error Result

ERROR: cannot use subquery in check constraint

Expected Behavior

Claude Code should either:

1. Not generate CHECK constraints with subqueries for PostgreSQL
2. Add a comment warning that the constraint must be enforced in application code
3. Suggest application-level validation instead

Correct Approach

-- ✅ VALID
CREATE TABLE utmembershipproduct (
id uuid PRIMARY KEY,
feeterm uuid NOT NULL,
membershipproductoptions uuid[],

-- NOTE: Complex validation rules are enforced in application code
-- due to PostgreSQL limitations with subqueries in CHECK constraints.
-- Business rules:
-- 1. Cannot use BONUSNEXTFISCAL option with LIFE feeterm
-- 2. Cannot use PRORATED option with LIFE feeterm
-- 3. Cannot combine PRORATED with BONUSNEXTFISCAL
);

With corresponding application validation:
public async Task<(bool IsValid, string? ErrorMessage)> ValidateProduct(Product product)
{
var lifeFeeTermCode = await _repository.GetByValTypeCodeAndValCode("FEETERM", "LIFE");
var bonusCode = await _repository.GetByValTypeCodeAndValCode("MEMBERCARTOPTIONS", "BONUSNEXTFISCAL");

bool isLifetime = lifeFeeTermCode != null && product.FeeTerm == lifeFeeTermCode.Id;
bool hasBonusNextFiscal = bonusCode != null &&
Array.Exists(product.Options, opt => opt == bonusCode.Id);

if (isLifetime && hasBonusNextFiscal)
{
return (false, "Cannot use BONUSNEXTFISCAL option with LIFE feeterm");
}

return (true, null);
}

Impact

• Severity: High - Prevents database deployment
• Frequency: Occurred multiple times in our table generation
• Workaround: Manual editing of generated scripts + application-level validation

Suggestion

Add PostgreSQL-specific constraint limitations to Claude Code's knowledge base, specifically:

• CHECK constraints cannot contain subqueries
• CHECK constraints cannot reference other tables
• Complex validation should be moved to application layer
• Provide guidance on when to use triggers vs. application validation

Related Database Engines

This limitation is specific to PostgreSQL. Other databases have different restrictions:

• MySQL: Also doesn't support subqueries in CHECK constraints
• SQL Server: Allows some subqueries but with restrictions
• Oracle: More permissive but still has limitations

Additional Context

We're working on a multi-tenant association management system with complex business rules around membership products. The validation rules involve cross-table
lookups and array membership checks, which naturally led Claude Code to generate subquery-based constraints.

What Should Happen?

Claude Code should either:

1. Not generate CHECK constraints with subqueries for PostgreSQL
2. Add a comment warning that the constraint must be enforced in application code
3. Suggest application-level validation instead

Error Messages/Logs

Steps to Reproduce

Described in the Claude-generated description.

Claude Model

Sonnet (default)

Is this a regression?

I don't know

Last Working Version

_No response_

Claude Code Version

2.0.28

Platform

Anthropic API

Operating System

Windows

Terminal/Shell

VS Code integrated terminal

Additional Information

_No response_