I have three tables:



1.  vacancies (PK is vacancyID)

2.  vacancies_locations (PK is vacancyID and locationID)

3.  vacancies_sectorID (PK is vacancyID and sectorID)



Foreign Key constraints have been implemented between the tables using the 

vacancyID field.



When I try to delete records using the following statement (please ignore 

any wrapping):



sql = "DELETE FROM vacancies WHERE vacancyID=" & vacancyID & ";"

sql = sql & "DELETE FROM vacancies_sectors WHERE vacancyID=" & vacancyID 

& ";"

sql = sql & "DELETE FROM vacancies_locations WHERE vacancyID=" & vacancyID 

& ";"

objCon.Execute sql



I get the following error:



DELETE statement conflicted with COLUMN REFERENCE 

constraint 'FK_vacancies_locations_vacancies'. The conflict occurred in 

database 'jobsite', table 'vacancies_locations', column 'vacancyID'.



I think I know what's causing the error, i.e. I'm violating referenital 

integrity.  But I don't think there's anything wrong with the db design 

and so how to I get around this?  Do I have to introduce and use a new 

field to establish the FK relationship between the tables?

First u must delete specified records from FK tables (i-e

vacancies_locations and vacancies_sectorID), after that u will be able to

delete these records from PK table. In ur SQL string u r trying to delete

the records from PK table first than the FK tables. 



-----Original Message-----

From: Jake Chapman [mailto:jakech@b...]

Sent: 22. november 2001 14:15

To: ASP Databases

Subject: [asp_databases] Deleting tables and FK constraint





I have three tables:



1.  vacancies (PK is vacancyID)

2.  vacancies_locations (PK is vacancyID and locationID)

3.  vacancies_sectorID (PK is vacancyID and sectorID)



Foreign Key constraints have been implemented between the tables using the 

vacancyID field.



When I try to delete records using the following statement (please ignore 

any wrapping):



sql = "DELETE FROM vacancies WHERE vacancyID=" & vacancyID & ";"

sql = sql & "DELETE FROM vacancies_sectors WHERE vacancyID=" & vacancyID 

& ";"

sql = sql & "DELETE FROM vacancies_locations WHERE vacancyID=" & vacancyID 

& ";"

objCon.Execute sql



I get the following error:



DELETE statement conflicted with COLUMN REFERENCE 

constraint 'FK_vacancies_locations_vacancies'. The conflict occurred in 

database 'jobsite', table 'vacancies_locations', column 'vacancyID'.



I think I know what's causing the error, i.e. I'm violating referenital 

integrity.  But I don't think there's anything wrong with the db design 

and so how to I get around this?  Do I have to introduce and use a new 

field to establish the FK relationship between the tables?

 



aftab.ahmad@k...


$subst('Email.Unsub')