delete records from self referencing table

[fikir]

Hello every one, hope you had a wonderful july 4th weekend

I have a patientVisit table wich is referencing itself, here is the table

patientVisit

visit_ID Visitor_ID Visitor_Name VisitDate LastVisit
1 1001 aab 01/01/07 Null
2 1001 aab 03/01/07 1
3 1002 Kal 04/04/06 Null
4 1003 Al 06/05/05 Null
5 1002 Kal 05/05/07 3
6 1001 aab 05/03/07 2
7 1002 Kal 07/07/07 5

A foriegn key is defined on LastVisit referening VisitID

I want to delete all records from PatientVisit table

if I do 'delete from patientVisit', the statement fails because it is there is a foreigh key reference.
How doI write a statement which can delete every record in the patientVisit table

Thanks,

 

[AlexCuse]

You need to either remove the foreign key constraint or delete from LastVisit first.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Crowley16]

temporarily drop the relationship, then delete the records, then reset the relationship...

--------------------
Procrastinate Now!

 

[fikir]

Thank you for your quick answer,
But there are 100'f of tables like this and each tables have a foreigh key relation to another tables as well, it is hard for me to track each foreign key defined on each table and drop it delete the data and recreate it again,

Is there an easy way to to delete a data for 100's of tables which are self-referenciing and have aother foreign key relation as well

Thanks,

 

[Crowley16]

100's of tables?

are you trying to completely empty your database of all tables?

why not just generate a script to recreate the tables and relationships, then just drop everything and create it all again?

--------------------
Procrastinate Now!

 

[AlexCuse]

Yes, by removing the relationship that prevents your delete from taking place.

I don't believe I would recommend this course of action, but it should be noted that this 'easy' way was suggested by both of the above posts. IMO you should not mess with protection of your data integrity in the name of making things easy. It is opening the door for future problems.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[fikir]

No it is for patient visit only not all tables, there might be 100 tables interrelated for this schema, my database has not less than 500 tables

thanks,

 

[bborissov]

You could first UPDATE LastVisit field to be NULL and the delete records. Not tested though.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fikir]

Thanks borislav,

That was what I was planning to do,

one more thing how do I check that a table is foreign key refernenced by some other table, if so how do I query out the table name and the constraint_name and the column on wich the constriant is defined, I have this code but it is not giving me the write one

SELECT 
    FK_Table  = FK.TABLE_NAME, 
    FK_Column = CU.COLUMN_NAME, 
    PK_Table  = PK.TABLE_NAME, 
    PK_Column = PT.COLUMN_NAME, 
    Constraint_Name = C.CONSTRAINT_NAME 
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
        SELECT 
            i1.TABLE_NAME, i2.COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
            INNER JOIN 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
            ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
            WHERE i1.CONSTRAINT_TYPE = 'foreign KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME 
where PK.TABLE_NAME like 'MONITORING_SITE_VISIT_SCHEDULE'

 

[fikir]

okay I want to make easy this one

create table tmp (visit_ID int primary key
,Visitor_ID int
,Visitor_Name varchar(100)
,VisitDate datetime
,LastVisit int constraint fk REFERENCES tmp)

insert into tmp
select 1, 1001, 'aab', '01/01/07', Null
union all select 2, 1001, 'aab', '03/01/07', 1
union all select 3, 1002, 'Kal', '04/04/06', null
union all select 4, 1003, 'Al', '06/05/05', Null
union all select 5, 1002, 'Kal', '05/05/07', 3
union all select 6, 1001, 'aab', '05/03/07', 2
union all select 7, 1002, 'Kal', '07/07/07', 5


Now if I want to delete Visit_ID = 3, because this ID is referenced by visit_ID 5, I have to first delete ID 5, visit_ID 5 is also referenced by Visit_ID 7 so I have to delete ID 7; becuase ID 7 is not referenced by no ID, I delete ID 7 then ID 5 then ID 3,

IS this doable in single query, if so how can we do it,
I really appreciate for your help on this

Thanks,

 

[fikir]

I wrote this procedure to do it recursively but it is erroring out
could some body comment on that

create proc delPro @input int
as
 declare @var int
 
  if not exists(select LastVisit from tmp 
                where LastVisit = @input)
  begin
     delete from tmp where visit_ID = @input
  end
  else 
  begin
     select @var = LastVisit 
     from tmp 
     where visit_ID = @input

     exec delPro @var

     delete from tmp where visit_ID = @input
   end

 exec delPro 3

Thanks,

 

[bborissov]

How bout:

create proc delPro @input int
as
  if not exists(select LastVisit from tmp
                where LastVisit > @input)
     begin
        delete from tmp where visit_ID = @input
     end
  else
     begin
        DECLARE @var int
        SELECT @var = MAX(LastVisit)
               FROM tmp WHERE LastVisit > @input
        exec delPro @var
     end

exec delPro 3

(note tested at all, and I have some beer so test it properly )

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fikir]

I have this error

Msg 547, Level 16, State 0, Procedure delPro, Line 6
DELETE statement conflicted with COLUMN SAME TABLE REFERENCE constraint 'fk'. The conflict occurred in database 'database1', table 'tmp', column 'LastVisit'.
The statement has been terminated.

(0 row(s) affected)

 

[bborissov]

try with:

SET IMPLICIT_TRANSACTIONS OFF
exec delPro 3
SET IMPLICIT_TRANSACTIONS ON

THIS IS A VERY DANGEROUS! Make a very good backup first.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP