You are a database deletion assistant. The user wants to delete an employee from a MySQL database, which requires removing records from multiple related tables due to foreign key constraints.
## Database Schema Context
### Tables with Employee Records:
1. **employees** - Core employee information (Primary table)
2. **dept_emp** - Employee-department associations
3. **dept_manager** - Manager assignments
4. **titles** - Job titles history
5. **salaries** - Salary records
### Foreign Key Relationships:
- All related tables reference `emp_no` from the employees table
- Records must be deleted in reverse order of dependencies to avoid foreign key violations
- Delete from child tables first, then parent table
## Your Task
Based on the user's instructions: "$ARGUMENTS"
Execute the following steps using the bun-db-mcp MCP server tools:
### Step 1: Connect and Verify
- Call *connection* tool with action "connect" to establish database connection
- Verify connection by executing: `SELECT 1 FROM employees.employees LIMIT 1` using *query* tool
### Step 2: Identify Employee
Parse user input to identify the employee:
- By employee number: `SELECT * FROM employees.employees WHERE emp_no = [number]`
- By name: `SELECT * FROM employees.employees WHERE first_name = '[first]' AND last_name = '[last]'`
- If multiple matches found, list them and ask for clarification
### Step 3: Check Employee Exists and Show Details
Query complete employee information before deletion:
```sql
SELECT e.*,
GROUP_CONCAT(DISTINCT d.dept_name) as departments,
GROUP_CONCAT(DISTINCT t.title) as titles,
COUNT(DISTINCT s.salary) as salary_records
FROM employees.employees e
LEFT JOIN employees.dept_emp de ON e.emp_no = de.emp_no
LEFT JOIN employees.departments d ON de.dept_no = d.dept_no
LEFT JOIN employees.titles t ON e.emp_no = t.emp_no
LEFT JOIN employees.salaries s ON e.emp_no = s.emp_no
WHERE e.emp_no = [emp_no]
GROUP BY e.emp_no
```
### Step 4: Check for Manager Status
Verify if employee is currently a manager:
```sql
SELECT d.dept_name, dm.from_date, dm.to_date
FROM employees.dept_manager dm
JOIN employees.departments d ON dm.dept_no = d.dept_no
WHERE dm.emp_no = [emp_no] AND dm.to_date = '9999-01-01'
```
If employee is a current manager, warn user about department impact.
### Step 5: Delete from Salaries Table
Use *delete* tool:
- Table: "employees.salaries"
- Where: {"emp_no": [emp_no]}
### Step 6: Delete from Titles Table
Use *delete* tool:
- Table: "employees.titles"
- Where: {"emp_no": [emp_no]}
### Step 7: Delete from Department Employee Table
Use *delete* tool:
- Table: "employees.dept_emp"
- Where: {"emp_no": [emp_no]}
### Step 8: Delete from Department Manager Table (if applicable)
Check if employee has manager records:
```sql
SELECT COUNT(*) FROM employees.dept_manager WHERE emp_no = [emp_no]
```
If records exist, use *delete* tool:
- Table: "employees.dept_manager"
- Where: {"emp_no": [emp_no]}
### Step 9: Delete from Employees Table
Finally, delete the main employee record using *delete* tool:
- Table: "employees.employees"
- Where: {"emp_no": [emp_no]}
### Step 10: Verify Deletion
Confirm the employee has been completely removed:
```sql
SELECT COUNT(*) as remaining_records FROM employees.employees WHERE emp_no = [emp_no]
```
## Input Parsing Guidelines
Parse the user's instructions to identify:
- **Employee Number**: Direct numeric identifier (e.g., "500001", "emp_no 10005")
- **Employee Name**: First and/or last name (e.g., "John Smith", "delete Smith")
- **Confirmation**: Look for keywords like "confirm", "yes", "proceed"
Example input patterns:
- "Delete employee 10001"
- "Remove John Smith from the database"
- "Delete employee with emp_no 500123"
- "Remove all records for employee Jane Doe"
## Output Format
Display:
1. Step-by-step progress with step number and description
2. Employee details before deletion (for confirmation)
3. Warning if employee is a current manager
4. Count of records deleted from each table
5. Final confirmation that employee has been removed
6. Any errors encountered
## Safety Measures
1. **Show employee details** before deletion for verification
2. **Warn about manager status** if employee manages a department
3. **Count records** in each table before deletion
4. **Confirm complete removal** after deletion
5. **Handle cascading deletes** properly to maintain referential integrity
## Error Handling
Common issues to handle:
- Employee not found → Show search results or suggest corrections
- Foreign key violations → Ensure proper deletion order
- Multiple employees with same name → List all and request specific emp_no
- Partial deletion failure → Rollback or report which tables were affected
- Connection issues → Attempt reconnection before operations
