dkim18
asked on
deleting dependencies
Hi Experts,
I am trying to delete a row from DEMOGRAPHICS table, but it has integrity constraint. I need to delete dependencies first. I do see SQL error such as:
ORA-02292: integrity constraint (xxx_xxx.FK_xxx_xxx) violated - child record found.
How do I find child record and delete it?
I am using oracle SQL developer.
thx,
I am trying to delete a row from DEMOGRAPHICS table, but it has integrity constraint. I need to delete dependencies first. I do see SQL error such as:
ORA-02292: integrity constraint (xxx_xxx.FK_xxx_xxx) violated - child record found.
How do I find child record and delete it?
I am using oracle SQL developer.
thx,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am trying to see what table it references, so I used this:
But I got this error.
SQL Error: ORA-00904: "FK_VISIT_NOTE_DEMOG": invalid identifier
select patient_id from demographics where patient_id in (select FK_VISIT_NOTE_DEMOG from demographics where patient_id=7531);
But I got this error.
SQL Error: ORA-00904: "FK_VISIT_NOTE_DEMOG": invalid identifier
FK_VISIT_NOTE_DEMOG is the 'name' of the constraint. You need to know the column name in the table to run that SQL.
That is a round-about way to get what you need. You need to track down the table_name of FK_VISIT_NOTE_DEMOG. then the FK column name, then delete those FK values where patient_id=7531
That is a round-about way to get what you need. You need to track down the table_name of FK_VISIT_NOTE_DEMOG. then the FK column name, then delete those FK values where patient_id=7531
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
... the best way is to query the parent table (find it , as I mentioned above, but if you're the only one that access the data, and your integrity is under your control, you will find with this other second method quite interesting... for example, if there are many other FK's pointing to the parent table, you will get them all (with constraint_name, table_name and row_id's).