Join 117,527 Programmers for FREE! Ask your question and get quick answers from experts. There are 1,989 online right now! We've got more than 500 tutorials and 2,000 snippets. Join and find out why Dream.In.Code is the #1 programming help community on the internet! Registration is fast and FREE... Join Now!
I am trying to Delete rows from a table that has a foreign key on one of the columns but when i try to execute i get...
Msg 50000, Level 16, State 1, Procedure bat_RethrowError, Line 49 Error: 547, Level: 16, State: 0, Procedure: bat_MoveSheetsToHistory_NonCall, Line: 262, Message: The DELETE statement conflicted with the REFERENCE constraint "FK_Scheduling_SheetStatus_SheetID". The conflict occurred in database "db", table "table.SheetStatus", column 'SheetID'
Here is my Delete statement
CODE
DELETE Scheduling.SheetStatus FROM Scheduling.SheetStatus AS SSS INNER JOIN ( SELECT * FROM @sheetsTable ) AS ST ON (SSS.SheetID = ST.SheetID) AND (SSS.AgentsSignedUp = ST.AgentsSignedUp);
any idea what i am doing wrong?
Ok...i think it is actually erroring when i try to delete from the Parent table not from the child table...i do need to delete from both tables. IE. Delete all from Child table that have the same SheetID as in the parent table, but i was doing this in 2 different delete statements; Parent first and then Child. is there a way to delete from them both at once?
This post has been edited by rastaman832003: 17 Jun, 2008 - 12:32 PM
how would i set that in this,When the Primary Key for the table in this Delete is the SkillID, which is the Foreign Key in the table for the Delete statement i posted above?
CODE
DELETE Scheduling.Sheets FROM Scheduling.Sheets AS SS INNER JOIN ( SELECT * FROM @sheetsTable ) AS ST ON (SS.StartsOn = ST.StartsOn) AND (SS.EndsOn = ST.EndsOn) AND (SS.Rate = ST.Rate) AND (SS.SkillID = ST.SkillID) AND (SS.PaySourceID = ST.PaySourceID) AND (SS.SheetID = ST.SheetID);
This post has been edited by rastaman832003: 17 Jun, 2008 - 01:23 PM
i think it is actually erroring when i try to delete from the Parent table not from the child table...i do need to delete from both tables.
Correct! A foreign key says, "there will be no orphans." Meaning the table that has the FK contraint enforces that a parent exists when you add a record. Conversly, when you try to delete a parent that has children, you'll basically get the error you did. You can delete children, the FK constraint doesn't care about that.
What you need to do is find the table that owns the contraint and kill the rows the relate to the parent.
Here's some code that will find all the tables that of FK constraints against your table.
CODE
SELECT FK.TABLE_SCHEMA, FK.TABLE_NAME, FK.CONSTRAINT_NAME, CU.COLUMN_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 WHERE PK.TABLE_SCHEMA='Scheduling' and PK.TABLE_NAME = 'SheetStatus' order by FK.TABLE_SCHEMA, FK.TABLE_NAME, FK.CONSTRAINT_NAME, CU.COLUMN_NAME, CU.ORDINAL_POSITION
I'm going to assume that one of the kids was "table.SheetStatus", given your error. ( Bad name, that, but it's what we have to work with. ) Before we kill the parents, we kill the kids.
CODE
DELETE table.SheetStatus FROM table.SheetStatus a INNER JOIN @sheetsTable b ON a.SheetID = b.SheetID
Then the parents:
CODE
DELETE Scheduling.SheetStatus FROM table.SheetStatus a INNER JOIN @sheetsTable b ON a.SheetID = b.SheetID
You have to adjust based on column names, of course.
Right now i have the delete for the Parent first, So if i delete the info from the Child table first then i shouldnt run into the foriegn key issue at all?
As noted previously, you can define a foreign key to automatically drop the child tables. I often prefer to do it manually, having the logic in procedures and yelling at those who might delete something by mistake.
This post has been edited by baavgai: 19 Jun, 2008 - 08:08 AM
As noted previously, you can define a foreign key to automatically drop the child tables. I often prefer to do it manually, having the logic in procedures and yelling at those who might delete something by mistake.
Yea...i think in my case its best to have the Delete logic for both instead of having the Delete from Parent cascade down to the child. Thanks, you tha man Baavgai!
ok...after changing the order of the select staement i have no more issue with the foriegn key...but i have a new issue... I have 2 variable, one is a smallint that represents the number of days before something should be moved to a new table, the second is a datetime value that represents the cut off date for something to be deleted(based on the date run)....so i am getting a arithmetic overflow error when using this code...
/* * If @archiveDays is zero, then the cut-off date is now, otherwise Set the cutoff date to * {@archiveDays} prior to today and the cutoff time to the end of that day. */ IF (@archiveDays = 0) BEGIN SET @deleteCutOffDate = GetDate(); END ELSE BEGIN SET @deleteCutOffDate = DateAdd(day, -archiveDays, GetDate()); SET @deleteCutOffDate = Cast((Convert(varchar, @deleteCutOffDate , 101) + ' 23:59:59') AS datetime); END;
any idea of another way i could convert these so as not to get the error?
Try making @archiveDays an int, just to be on the safe side. However, I ran this at work without issue, so I'm not sure where you're getting hit.
Here's my test code:
CODE
declare @archiveDays int declare @deleteCutOffDate datetime
set @archiveDays=8 IF (@archiveDays = 0) BEGIN SET @deleteCutOffDate = GetDate(); END ELSE BEGIN SET @deleteCutOffDate = DateAdd(day, -@archiveDays, GetDate()); SET @deleteCutOffDate = Cast((Convert(varchar, @deleteCutOffDate , 101) + ' 23:59:59') AS datetime); END;
17 Jun, 2008 - 11:34 AM
| 