SQL Server 2000 question

Evil Phil

Folks,

I have three tables in a db. Question, Answer, and Response as below

Question
==================
QuestionId int identity
ModuleId nvarchar
ResManRef nvarchar
Question nvarchar
QuestionTypeId nvarchar
QuestionOrder int
CorrectAnswerId int
IsActive bit
CreatedBy nvarchar
CreateDate smalldatetime
ModifiedBy nvarchar
ModifiedDate smalldatetime

Answer
==================
AnswerId int identity
QuestionId int
ResManRef nvarchar
Answer nvarchar
AnswerOrder nvarchar
IsActive bit
CreatedBy nvarchar
CreateDate smalldatetime
ModifiedBy nvarchar
ModifiedDate smalldatetime

Response
==================
ResponseId int identity
ModuleSessionId int
QuestionId int
ResponseAnswerId int
CreatedBy nvarchar
CreateDate smalldatetime
ModifiedBy nvarchar
ModifiedDate smalldatetime

There are a number of constraints on these tables which are preventing me from deleting records (Cascade is off for all btw). Constraints:

Question
FOREIGN KEY FK_Question_Answer CorrectAnswerId REFERENCES aer.dbo.Answer (AnswerId)

Answer
FOREIGN KEY FK_Answer_Question QuestionId REFERENCES aer.dbo.Question (QuestionId)

Response

FOREIGN KEY FK_Response_Answer ResponseAnswerId REFERENCES aer.dbo.Answer (AnswerId)
FOREIGN KEY FK_Response_Question QuestionId REFERENCES aer.dbo.Question (QuestionId)

I'm trying to delete records based on QuestionId but the constraints are preventing me from doing so. This is an inherited system so we can't drop or change the constraints as we don't have time for regression testing. I'm pulling my hair out trying to write a script. The relationships seem to be circular which is preventing this. Can anybody see a way out other than dropping and re-creating the relationships?

Evil Phil

Well we're going to drop and recreate the relationships but if anybody does have an answer it would be greatly appreciated.

bonkey

UPDATE Question SET CorrectAnswerId = null WHERE QuestionID = idToDelete

DELETE FROM Response where ResponseAnswerID IN (SELECT AnswerId FROM Answer WHERE QuestionID = idToDelete)

DELETE FROM Response where QuestionId = idToDelete

DELETE FROM Answer  WHERE QuestionID = idToDelete
DELETE FROM Question WHERE QuestionID = idToDelete

Should do it for ya Phil. some fo the DELETEs may be redundant if the references are strict.

If you can't set the CorrectAnswerId to null, then you could have problems, but you can probably work around them creating dummy records* in Question and Answer which have a special "deleteMeId", and you can update to that instead of to Null.

Alternately, I know its possible to disable Constraints, and I believe its possible to re-enable them without getting them to recheck every record for validity, whcih would also be an option, but not one I'd go for by choice.

That do it for ya?

jc

* dummy records would be permanent, not just created for each delete operation.

bonkey

Damn my timing.

Evil Phil

Your timing is good Bonkey, and thanks again. CorrectAnswerId does allow nulls.

Evil Phil

Actually its just been pointed out to us (and we are kicking ourselves soundly for missing it).

Question
==================
QuestionId int identity
ModuleId nvarchar
ResManRef nvarchar
Question nvarchar
QuestionTypeId nvarchar
QuestionOrder int
CorrectAnswerId int
IsActive bit
CreatedBy nvarchar
CreateDate smalldatetime
ModifiedBy nvarchar
ModifiedDate smalldatetime

Set that to false, problem solved :rolleyes: