关于ProjectLayerContent表三个自引用多对多关系的SQL查询（查询/删除）需求

Alright, let's tackle these three self-referential many-to-many relationships in your ProjectLayerContent table. I'll break down each relationship with clear SELECT queries to retrieve the data and safe DELETE statements to manage the records, plus some notes to keep your data integrity intact.

1. Parent-Child Hierarchy Relationship #

This relationship uses ProjectLayerContentParentID to link a content item to its parent (or null if it has no parent).

SELECT Queries #

First, here's how to pull all parent-child pairs, including items without a parent:

-- Get all content items with their parent (if exists)
SELECT
    parent.ProjectLayerContentID AS ParentID,
    parent.ProjectLayerContentName AS ParentName,
    child.ProjectLayerContentID AS ChildID,
    child.ProjectLayerContentName AS ChildName
FROM
    ProjectLayerContent child
LEFT JOIN
    ProjectLayerContent parent ON child.ProjectLayerContentParentID = parent.ProjectLayerContentID
ORDER BY
    parent.ProjectLayerContentID, child.ProjectLayerContentID;

If you only want items that have an explicit parent:

-- Get only child items with a valid parent
SELECT
    parent.ProjectLayerContentID AS ParentID,
    parent.ProjectLayerContentName AS ParentName,
    child.ProjectLayerContentID AS ChildID,
    child.ProjectLayerContentName AS ChildName
FROM
    ProjectLayerContent child
INNER JOIN
    ProjectLayerContent parent ON child.ProjectLayerContentParentID = parent.ProjectLayerContentID
ORDER BY
    parent.ProjectLayerContentID, child.ProjectLayerContentID;

DELETE Statements #

Critical note: If ProjectLayerContentParentID is a foreign key pointing back to the table, you'll need to delete child items before their parent to avoid constraint violations.

-- Option 1: Delete all child items of a specific parent first, then delete the parent
-- Replace @TargetParentID with your actual parent ID
DELETE FROM ProjectLayerContent
WHERE ProjectLayerContentParentID = @TargetParentID;

DELETE FROM ProjectLayerContent
WHERE ProjectLayerContentID = @TargetParentID;

-- Option 2: If your foreign key has ON DELETE CASCADE enabled, you can delete the parent directly
-- Child items will be deleted automatically
DELETE FROM ProjectLayerContent
WHERE ProjectLayerContentID = @TargetParentID;

-- Clean up orphaned child items (those pointing to a non-existent parent)
DELETE FROM ProjectLayerContent
WHERE ProjectLayerContentParentID IS NOT NULL
AND NOT EXISTS (
    SELECT 1 FROM ProjectLayerContent p
    WHERE p.ProjectLayerContentID = ProjectLayerContentParentID
);

2. Scope Inclusion Relationship #

This relationship activates when ScopeIsProjectLayerContent = 1, where ScopeIsProjectLayerContentID links to another content item that the current item's scope includes.

SELECT Query #

-- Get all valid scope inclusion pairs
SELECT
    source.ProjectLayerContentID AS SourceContentID,
    source.ProjectLayerContentName AS SourceContentName,
    target.ProjectLayerContentID AS IncludedScopeID,
    target.ProjectLayerContentName AS IncludedScopeName
FROM
    ProjectLayerContent source
INNER JOIN
    ProjectLayerContent target ON source.ScopeIsProjectLayerContentID = target.ProjectLayerContentID
WHERE
    source.ScopeIsProjectLayerContent = 1
ORDER BY
    source.ProjectLayerContentID;

DELETE Statements #

You can delete specific inclusion relationships, all relationships pointing to a target, or invalid ones:

-- Delete a specific scope inclusion pair
-- Replace @SourceContentID and @IncludedScopeID with your values
DELETE FROM ProjectLayerContent
WHERE
    ScopeIsProjectLayerContent = 1
    AND ProjectLayerContentID = @SourceContentID
    AND ScopeIsProjectLayerContentID = @IncludedScopeID;

-- Delete all scope relationships that include a specific target content item
DELETE FROM ProjectLayerContent
WHERE
    ScopeIsProjectLayerContent = 1
    AND ScopeIsProjectLayerContentID = @TargetIncludedID;

-- Delete invalid scope relationships (pointing to a non-existent content item)
DELETE FROM ProjectLayerContent
WHERE
    ScopeIsProjectLayerContent = 1
    AND NOT EXISTS (
        SELECT 1 FROM ProjectLayerContent t
        WHERE t.ProjectLayerContentID = ScopeIsProjectLayerContentID
    );

3. Content Nesting Relationship #

This kicks in when DataTypeIsContentOfContent = 1, where ContentOfContentID links to another content item that the current item is nested within.

SELECT Query #

-- Get all valid content nesting pairs
SELECT
    nested.ProjectLayerContentID AS NestedContentID,
    nested.ProjectLayerContentName AS NestedContentName,
    parentContent.ProjectLayerContentID AS ParentContentID,
    parentContent.ProjectLayerContentName AS ParentContentName
FROM
    ProjectLayerContent nested
INNER JOIN
    ProjectLayerContent parentContent ON nested.ContentOfContentID = parentContent.ProjectLayerContentID
WHERE
    nested.DataTypeIsContentOfContent = 1
ORDER BY
    nested.ProjectLayerContentID;

DELETE Statements #

Again, prioritize data integrity when deleting nested items:

-- Delete a specific content nesting relationship
-- Replace @NestedContentID and @ParentContentID with your values
DELETE FROM ProjectLayerContent
WHERE
    DataTypeIsContentOfContent = 1
    AND ProjectLayerContentID = @NestedContentID
    AND ContentOfContentID = @ParentContentID;

-- Delete all nested items that reference a specific parent content item
DELETE FROM ProjectLayerContent
WHERE
    DataTypeIsContentOfContent = 1
    AND ContentOfContentID = @TargetParentContentID;

-- Delete invalid nesting relationships (pointing to a non-existent content item)
DELETE FROM ProjectLayerContent
WHERE
    DataTypeIsContentOfContent = 1
    AND NOT EXISTS (
        SELECT 1 FROM ProjectLayerContent p
        WHERE p.ProjectLayerContentID = ContentOfContentID
    );

General Safety Note #

Always run a corresponding SELECT query first to verify which records will be deleted before executing any DELETE statements. Backing up your data beforehand is also a smart move to avoid accidental data loss.

内容的提问来源于stack exchange，提问作者Mostafa Elabbady