get_table_constraints
Retrieve primary keys, foreign keys, unique, and check constraints for Oracle database tables to understand table relationships and enforce data integrity.
Instructions
Get constraints (primary keys, foreign keys, unique, check) for a table
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| table_name | Yes | Table name | |
| schema | No | Schema name (optional, searches all accessible schemas if not specified) |
Implementation Reference
- src/index.js:455-504 (handler)The handler function for 'get_table_constraints' tool. Executes a SQL query against Oracle's data dictionary views (all_constraints, all_cons_columns) to retrieve table constraints (PK, UQ, FK, CHECK) including details like columns involved and referenced tables.
async handleGetTableConstraints(args) { const query = ` SELECT c.owner AS schema_name, c.constraint_name, c.constraint_type, c.status, c.validated, CASE WHEN c.constraint_type = 'C' THEN 'CHECK CONSTRAINT' ELSE NULL END as search_condition, LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS columns, r.table_name AS referenced_table, r.constraint_name AS referenced_constraint FROM all_constraints c LEFT JOIN all_cons_columns cc ON c.constraint_name = cc.constraint_name AND c.owner = cc.owner LEFT JOIN all_constraints r ON c.r_constraint_name = r.constraint_name AND c.r_owner = r.owner WHERE c.table_name = :1 ${args.schema ? 'AND c.owner = :2' : ''} GROUP BY c.owner, c.constraint_name, c.constraint_type, c.status, c.validated, r.table_name, r.constraint_name ORDER BY c.owner, CASE c.constraint_type WHEN 'P' THEN 1 WHEN 'U' THEN 2 WHEN 'R' THEN 3 WHEN 'C' THEN 4 ELSE 5 END, c.constraint_name `; const params = [args.table_name.toUpperCase()]; if (args.schema) { params.push(args.schema.toUpperCase()); } const result = await this.executeQuery(query, params); return { content: [ { type: 'text', text: JSON.stringify(result.rows, null, 2) } ] }; } - src/index.js:247-264 (registration)Tool registration in listTools response, including name, description, and input schema definition.
{ name: 'get_table_constraints', description: 'Get constraints (primary keys, foreign keys, unique, check) for a table', inputSchema: { type: 'object', properties: { table_name: { type: 'string', description: 'Table name' }, schema: { type: 'string', description: 'Schema name (optional, searches all accessible schemas if not specified)' } }, required: ['table_name'] } }, - src/index.js:294-295 (registration)Dispatch case in CallToolRequest handler that routes to the tool handler.
case 'get_table_constraints': return await this.handleGetTableConstraints(args);