SQL server: smazat rodičovské nody i potomky

[heretik]

Mam takuto tabulku, Nodes.ParentId ma constraint na Nodes.Id rootovsky node ma ako ParentId hodnotu null. Problem je ze pri tom constrainte neviem nastavit mazanie (DELETE CASCADE). Potreboval by som aby ked vymazem rodicovsky node aby sa automaticky zmazali vstky jeho deti. Ako na to?

Kód: [Vybrat]

CREATE TABLE [dbo].[Nodes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](120) NOT NULL,
[Body] [nvarchar](max) NOT NULL,
[BodyRaw] [nvarchar](max) NOT NULL CONSTRAINT [DF_Nodes_BodyRaw]  DEFAULT (''),
[ParentId] [int] NULL,
[Created] [datetime2](7) NOT NULL,
[LastModified] [datetime2](7) NOT NULL,
[IsPublished] [bit] NOT NULL CONSTRAINT [DF_Table_1_Published]  DEFAULT ((1)),
[IsRemoved] [bit] NOT NULL CONSTRAINT [DF_Nodes_IsRemoved]  DEFAULT ((0)),
[NodeType] [nvarchar](16) NOT NULL CONSTRAINT [DF_Nodes_NodeType]  DEFAULT (N'file'),
[Style] [nvarchar](8) NOT NULL CONSTRAINT [DF_Nodes_Style]  DEFAULT (N'default'),
[IsOpened] [bit] NOT NULL CONSTRAINT [DF_Nodes_IsOpened]  DEFAULT ((1)),
[Source] [varbinary](max) NULL,
[SourceMimeType] [varchar](32) NULL,
[PreviewSource] [varbinary](max) NULL,
[PreviewSourceMimeType] [varchar](32) NULL,
[Guid] [char](36) NULL,
[VisibleAttachments] [bit] NOT NULL CONSTRAINT [DF_Nodes_VisibleAttachments]  DEFAULT ((0)),
[IsDone] [bit] NOT NULL CONSTRAINT [DF_Nodes_Done]  DEFAULT ((0)),
 CONSTRAINT [PK_Nodes] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[Nodes]  WITH CHECK ADD  CONSTRAINT [FK_Nodes_Nodes] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Nodes] ([Id])
GO

ALTER TABLE [dbo].[Nodes] CHECK CONSTRAINT [FK_Nodes_Nodes]
GO

[Reklama]

[Youda]

Use google, Luke.

https://stackoverflow.com/questions/1433808/sql-server-cascade-delete-and-parent-child-table?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa