Delete rows in relational database (SQL Server)?

Question

Delete rows in relational database (SQL Server)?

Answer 1

Cascade Delete In Sql Server

Answer 2

opt for * FROM `your_table` shrink 0, 5 obv put in the call of your table in the 'your_table' section :) to describe somewhat further, 0 stands for the checklist you opt for to start with (undergo in strategies numbering in sq. starts with 0 rather of a million) and 5 is the style of returns you opt for entire.

Answer 3

DELETE Categories.*, Subcategories.* FROM Categories, Subcategories WHERE Categories.Id = [whatyouwant] && Categories.Id = Subcategories.CategoryId



Your tables should not be plural, so you now in a future, this is because your row doesn't explain categories but a category, so in the future name them Cateogry, Subcategory

Answer 4

You have two solution ...



first:

You can create a foreign key in SubCategory on the field CategoryID with the syntax:



CREATE TABLE SubCategory

(

ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

CategoryID INT NOT NULL DEFAULT(0)

FOREIGN KEY REFERENCES Category (ID) ON DELETE CASCADE,

Description NVARCHAR(30) NOT NULL DEFAULT('')

)



When you insert a record in SubCategory, the relation of FOREIGN KEY must be valid .. you can't insert a value in CategoryID that isn't in Category.ID values ..



And when you remove a reocrd from Category with the statement



DELETE FROM Category WHERE ID = x



the action of delete will be "applied" on SubCategory rows with the CategoryID value .. (you can specify the action on child tables...)



If you can't change the schema..



you can create a trigger on Category field



CREATE TRIGGER tr_CategoryDelete ON

Category AFTER DELETE

AS

DELETE FROM SubCategory WHERE CategoryID IN (SELECT ID from deleted)

GO



For detail get a free copy of sql server book's online (bol)

Answer 5

There are 2 ways to handle this.



1. In your code or a stored procedure, when you pass in the ID for categories, you first delete all the rows on SubCategories with the same parent ID then you can safely delete the row in Categories.



2. Look into setting up Cascading Deletes in SQLServer. I can not remeber the syntax or details, but when setup, the dataabse will handle deleting all dependant records in related tables for you when you attempt to delete the master or parent record.

Answer 6

Perform a cascading delete operation. It deletes all rows in the table specified that conform to the criteria selected, while also deleting any child/grandchild records—and so on.



You did not specify the environment in which you are operating. Therefore, I listed solutions in Access, SQL Server 2005, the .NET Framework, and also included two links to stored procedures.

_________________________________



One of the (few) very handy things about MS Access is the cascade delete function. If you delete a record from a parent table, all relating records in the child tables are also deleted.



http://office.microsoft.com/en-us/access/HA011739511033.aspx



http://search.microsoft.com/results.aspx?mkt=en-US&setlang=en-US&q=cascading+deletes

_________________________________



SQL Server 2005:



http://msdn2.microsoft.com/de-de/library/ms131460.aspx



SQLForeignKeys



SQL Server supports cascading updates and deletes through the foreign key constraint mechanism. SQL Server returns SQL_CASCADE for UPDATE_RULE and/or DELETE_RULE columns if CASCADE option is specified on the ON UPDATE and/or ON DELETE clause of the FOREIGN KEY constraints. SQL Server returns SQL_NO_ACTION for UPDATE_RULE and/or DELETE_RULE columns if NO ACTION option is specified on the ON UPDATE and/or ON DELETE clause of the FOREIGN KEY constraints.

_________________________________



Are you in a .NET environment?



http://msdn2.microsoft.com/en-us/library/st1t2c35(VS.71).aspx



All of the ForeignKeyConstraints have been set to:



Delete=Cascade

Update=Cascade

AcceptReject=None

_________________________________



Here is a stored procedure for a cascading delete:



http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7



Normally, if you try and delete a record from a table that is constrained by a foreign key, you’ll get an error message. This procedure checks for any foreign keys for the table, deletes any child records, then deletes the intended record.

It references the system tables sysforeignkeys, sysobjects and syscolumns. Sysforeignkeys does what it says on the tin—it’s a list of all foreign keys in the database. It doesn’t contain actual table and field names, instead it contains links to the sysobjects (tables, stored procedures, views etc) and syscolumns (fields).



The procedure works like this—if you want to delete a record from table X, you look in the sysforeignkeys table for all references where table X is the parent table. It may be involved in several such FK’s. All you do is recursively go through these FK’s, deleting the child table records that are linked to the record we want to delete.

_________________________________



Recursive row delete stored procedure:



http://weblogs.asp.net/sbehera/archive/2006/02/14/438200.aspx



This is designed to do the same sort of thing as Access's cascade delete function.



It first reads the sysforeignkeys table to find any child tables, then deletes the soon-to-be orphan records from them using recursive calls to this procedure. Once all child records are gone, the rows are deleted from the selected table. It is designed at this time to be run at the command line. It could also be used in code, but the printed output will not be available.

_________________________________