Activity for the SNOMED-CT

i have renamed the old sct_description table as the backup table "sct_description_bkp_26022026" now i have extracted the data from the original table which is now named as sct_description_bkp_2602202 on the basis of conceptid column in the table and referencedComponentId in the excel sheet tranfered all the data in the table which is "sct_description" old table have 2.4 million records and the table have 354 records.


Deeper Impact Analysis: With Current Schemas

Schema Highlights
t_benchiefcomplaint: FK still points to sct_description_bkp_26022026 (not the new sct_description). This means no immediate FK violations from your table reduction—yet.
sct_description: New table (354 rows), deduplicated by ConceptID (one row per unique ConceptID, latest version).
sct_description_bkp_26022026: Full backup (2.4M rows), with all historical versions.


Deeper Impacts from Reducing to 354 Rows


new sct_description keeps only 1 row per ConceptID (the most recent non-deleted). This means most SCTDesID in t_benchiefcomplaint will become "orphaned" if you switch the FK to the new table. “Orphaned” means a child row refers to a parent row that no longer exists.

for example->
t_benchiefcomplaint.SCTDesID is the child reference.
sct_description.sctDesID is the parent row.
If t_benchiefcomplaint.SCTDesID = 123 but sct_description has no row with sctDesID = 123, then that t_benchiefcomplaint row is orphaned.


If FK is enforced: DB will reject insert/update of orphaned rows.
For queries: joins like ... JOIN sct_description sd ON tbc.SCTDesID = sd.sctDesID will return no row for orphans (NULL fields / missing term).
For app logic: orphaned SCTDesID means missing SCT metadata for chief complaints.



1. FK Constraint Risk (If You Change It)
Currently: FK → sct_description_bkp_26022026 (safe).
If you alter the FK to REFERENCES sct_description (sctDesID), you'll get violations because:
t_benchiefcomplaint has ~962k rows (based on AUTO_INCREMENT).
Many SCTDesID values point to rows in the backup that aren't in the new table (since you deduplicated).
Impact: Inserts/updates to t_benchiefcomplaint will fail. Existing data might cause query errors if joins are added.


2. Data Integrity & Missing Descriptions
Unique ConceptID in Backup: Likely >354 (since backup has 2.4M rows, many duplicates per ConceptID).
Affected Rows in t_benchiefcomplaint: Most records will have SCTDesID pointing to missing rows in new sct_description.
Example: If a ConceptID had 10 versions in backup, only 1 is in new table. The other 9 SCTDesID are gone.
Impact: Chief complaints will lose SCT descriptions (SCTChiefComplaint, SCTCode). App queries joining on SCTDesID will return NULL or fail.


This done in UAT till now , we roll back this simply renaming the old table.