SQL Server Performance Forum – Threads Archive
Deletion of recrods
Hi there, I have around 90 tables, and Company table is the main table which is related to almost 70 tables in the databased(directely or indirectly) For example, the company with id = 2 hasrecrod in CompanyAddresses(companyId fk), CompanyOwnership(CompanyId fk), CompanyDocuments(companyId fk), Products (companyid FK), SubProducts(productId FK), ProductAttributes(ProductId fk) etc. Now I want to delete the recrod for companyId = 2, as the Foreign key consratains are defined i can’t delete the recrods unless i delete the recrods from all other tables. These are only some tables. there are aruond 70 tables which are related to the CompanyId. I have written some procedures, which do this thing. But for some tables like Attribute, one attributeid is referenced by more then 1 recrods in tables like ProductAttributes(AttributeId fk),EquipmentAttributes(attributeId fk), subproductAttributes(attributeid fk) etc. So every time I have to check whether the recrod is referenced by some other recrods in all these tables or not. it becomes very tidious and also time consuming. Is there any other way to do this thing?
Pls do let me know about your comments/ openions. Best Regards!
Pallavi
Hi Pallavi This is an extract from an article I read about CASCADE DELETE.I hope it helps you. SQL Server 2000 introduces many new features. Some of them fall into the constraints area. Now you can control the actions SQL Server 2000 takes when you attempt to update or delete a key to which existing foreign keys point. You can control it by using the new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. For example, in the previous versions of SQL Server if you wanted to do a cascade delete from the referenced table when the appropriate record in the parent table is deleted, you had to create a trigger which executed on delete of the parent table, but now you can simply specify the ON DELETE clause in the REFERENCES clause. The following example is used to create the Books and the Authors tables and create a foreign key constraint which will perform the cascade delete action, therefore, when a row in the Authors table is deleted, the corresponding rows in the Books are also deleted: CREATE TABLE Books (
BookID INT NOT NULL PRIMARY KEY,
AuthorID INT NOT NULL,
BookName VARCHAR(100) NOT NULL,
Price MONEY NOT NULL
)
GO CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO ALTER TABLE Books
ADD CONSTRAINT fk_author
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID) ON DELETE CASCADE
GO
cheers
Parik****
Hi Thanks for the solution,
Can you give me the link for this article. Best Regards!
Pallavi
You can search it in Article section if that article is from this site Madhivanan Failing to plan is Planning to fail
Yes..
Cascade Delete or Update should work for the same.
I used it and got it worked… Sony
Software Engineer
Bangalore
[email protected]
Hi Pallavi Here is the link http://www.mssqlcity.com/Articles/General/using_constraints.htm cheers
Parik****
]]>