July 30, 2008 at 5:24 am
Hi Every body
How can i delete having reference constraints in a table
July 30, 2008 at 5:29 am
Either turn on casade deletes so that the foreign key refernce row is deleted as well, or delete the foreign row first.
This needs to be done with cautioin though and you need to be aware of the implicaitons of deleting rows in other tables.
July 30, 2008 at 5:34 am
can u give syntax of this deleting with cascade
all referecing keys
July 30, 2008 at 6:27 am
You specify cascading when you create a table. You use the ON DELETE CASCADE clause.
In the example below, if you delete a line in the purchaseorder table, it will automatically delete the lines in purchaseorderline that corresponds to the puchaseorder.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PurchaseOrder](
[ID] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](100) NULL,
CONSTRAINT [PK_PurchaseOrder] PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO
CREATE TABLE [dbo].[PurchaseOrderLine](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PurchaseOrderID] [int] NULL,
CONSTRAINT [PK_PurchaseOrderLine] PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO
ALTER TABLE [dbo].[PurchaseOrderLine] WITH CHECK ADD CONSTRAINT [FK_PurchaseOrderLine_PurchaseOrder] FOREIGN KEY([PurchaseOrderID])
REFERENCES [dbo].[PurchaseOrder] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PurchaseOrderLine] CHECK CONSTRAINT [FK_PurchaseOrderLine_PurchaseOrder]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply