list_constraints

Retrieve all constraints for a PostgreSQL table, including primary keys, foreign keys, unique constraints, and check constraints, to understand table relationships and data integrity rules.

Instructions

List all constraints for a table (PK, FK, UNIQUE, CHECK).

Args:
    table_name: Name of the table
    schema: Schema name (default: public)
    
Returns:
    List of constraints with type, columns, and references

Input Schema

TableJSON Schema

Name	Required	Description	Default
`table_name`	Yes
`schema`	No		public

Implementation Reference

postgres_mcp/server.py:271-306 (handler)

MCP tool handler for 'list_constraints', decorated with @mcp.tool() for registration and execution. Processes raw constraints from client and groups multi-column ones.

@mcp.tool()
@handle_db_error
def list_constraints(table_name: str, schema: str = "public") -> dict:
    """List all constraints for a table (PK, FK, UNIQUE, CHECK).
    
    Args:
        table_name: Name of the table
        schema: Schema name (default: public)
        
    Returns:
        List of constraints with type, columns, and references
    """
    client = get_client()
    constraints = client.list_constraints(table_name, schema)
    
    # Group by constraint name to handle multi-column constraints
    grouped = {}
    for c in constraints:
        name = c["constraint_name"]
        if name not in grouped:
            grouped[name] = {
                "name": name,
                "type": c["constraint_type"],
                "columns": [],
                "references_table": c.get("references_table"),
                "references_column": c.get("references_column"),
                "check_clause": c.get("check_clause"),
            }
        if c.get("column_name"):
            grouped[name]["columns"].append(c["column_name"])
    
    return {
        "table_name": table_name,
        "schema": schema,
        "constraints": list(grouped.values()),
    }

postgres_mcp/postgres_client.py:333-370 (helper)

Core helper method in PostgresClient that executes SQL query against information_schema to fetch raw constraint data.

def list_constraints(self, table_name: str, schema: str = "public") -> list[dict]:
    """List constraints for a table.
    
    Args:
        table_name: Table name
        schema: Schema name
        
    Returns:
        List of constraint dicts
    """
    query = """
        SELECT 
            tc.constraint_name,
            tc.constraint_type,
            tc.table_name,
            kcu.column_name,
            ccu.table_name AS references_table,
            ccu.column_name AS references_column,
            cc.check_clause
        FROM information_schema.table_constraints tc
        LEFT JOIN information_schema.key_column_usage kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        LEFT JOIN information_schema.constraint_column_usage ccu
            ON tc.constraint_name = ccu.constraint_name
            AND tc.table_schema = ccu.table_schema
            AND tc.constraint_type = 'FOREIGN KEY'
        LEFT JOIN information_schema.check_constraints cc
            ON tc.constraint_name = cc.constraint_name
            AND tc.table_schema = cc.constraint_schema
        WHERE tc.table_schema = %s
            AND tc.table_name = %s
        ORDER BY tc.constraint_type, tc.constraint_name
    """
    with self.get_cursor() as cursor:
        cursor.execute(query, (schema, table_name))
        return [dict(row) for row in cursor.fetchall()]

postgres-mcp