59 lines
1.5 KiB
Python
59 lines
1.5 KiB
Python
"""
|
|
Comprehensive fix for all database foreign key constraints.
|
|
"""
|
|
import sqlite3
|
|
|
|
# Connect to SQLite database
|
|
db_path = 'db.sqlite3'
|
|
conn = sqlite3.connect(db_path)
|
|
cursor = conn.cursor()
|
|
|
|
print("Fixing all database constraints...")
|
|
|
|
# Disable foreign key checks
|
|
cursor.execute("PRAGMA foreign_keys = OFF;")
|
|
|
|
# Get all tables with tenant_id that have violations
|
|
tables_with_violations = [
|
|
'radiology_imaging_study',
|
|
'accounts_user',
|
|
'hr_department',
|
|
'hr_training_program',
|
|
'operating_theatre_operating_room',
|
|
'patients_patient_profile',
|
|
]
|
|
|
|
for table in tables_with_violations:
|
|
try:
|
|
# Delete rows with invalid tenant_id
|
|
cursor.execute(f"""
|
|
DELETE FROM {table}
|
|
WHERE tenant_id NOT IN (SELECT id FROM core_tenant)
|
|
""")
|
|
affected = cursor.rowcount
|
|
if affected > 0:
|
|
print(f" Deleted {affected} rows from {table} with invalid tenant_id")
|
|
except sqlite3.OperationalError as e:
|
|
print(f" Error fixing {table}: {e}")
|
|
|
|
# Commit changes
|
|
conn.commit()
|
|
|
|
# Re-enable foreign key checks
|
|
cursor.execute("PRAGMA foreign_keys = ON;")
|
|
|
|
# Verify constraints
|
|
cursor.execute("PRAGMA foreign_key_check;")
|
|
violations = cursor.fetchall()
|
|
|
|
if violations:
|
|
print(f"\n⚠ Still have {len(violations)} foreign key violations")
|
|
print("First 10 violations:")
|
|
for violation in violations[:10]:
|
|
print(f" {violation}")
|
|
else:
|
|
print("\n✓ All foreign key constraints are now valid!")
|
|
|
|
conn.close()
|
|
print("\nDatabase fix completed!")
|