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()), }
- 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()]