Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
FK for preventing parental deletions.
Message
General information
Forum:
Microsoft SQL Server
Category:
Database design
Miscellaneous
Thread ID:
00720778
Message ID:
00722986
Views:
17
>Are you sure? You mean if I have a foreign key relationship defined, I have to delete the child records before you delete the parent record. Mabey Im just reading this wrong...

Yes.

Or, if you are using SQL Server 2000 you can tell it to casecade the delete of the parent rather than disallow.

BOb


>
>The relationships work for updates and inserts. If I try to add or update a child record with a key that dosen't exist in the parent table it will throw an error. If I delete the parent record, the child records will still exist if cascade deletes are off, if cascading deletes are on, the child records will get deleted when the parent records is deleted.
>
>I think he is asking if you can define a fk constraint that when you try to delete the parent record, will give an error if there is any child records. If this is it. Im pretty sure there is no way to define a fk constraint for this. You would need to make a stored procedure to do the deletes. That would be pretty simple.
>
>
>create procedure deleteRec (@parentID int) as
>begin
>
>if not exists(select * from childTable where fk_key = @parentId)
> delete from parentTable where pk_key = @parentID
>else
>  raiserror(...)
>
>end
>
>
>
>
>>This is one of the functions of a Foreign Key Constraint. It will verify that the FK is not used in the related table before allowing the row to be deleted.
>>
>>
>>>How would I use a Foreign Key to prevent deletions on the parent table?
>>>
>>>For example, I have an Orders table and an OrderCancels table related by a FK on the iOrdID. When the app requests to delete a record on the Orders table, I need to check the OrderCancels table via the iOrdID FK for corresponding records, and if there are, not delete the order.
Previous
Reply
Map
View

Click here to load this message in the networking platform