sql query for category heirarchy validations

Question

I need to add validation on category creation.

CASE 1: parentId should be valid if supplied

CASE 2: name of sibling could not be duplicated

I have this table: (categories)

    id  | parentId  | name  
   -----|-----------|------
    1   | NULL      | CatA
    2   | 1         | CatA.1

(Note: My parent child hierarchy can go up-to nth level)

Now in the above scenario what should not be allowed is:

1. I cannot provide an invalid parentId
2. I cannot create a category with name: CatA where parentId = null
3. I cannot create a category where name: CatA.1 where parentId = 1

Now I am in a nodejs so I need to return these 2 validations errors:

1. The provided parentId is invalid
2. Duplicate name detected

Now I want to achieve this using a single optimized SQL query. I can use if else statements later on the base of query response. But for me it is really important that I use single query and that query should be as opptimized as possible.

What I tried so far is:

SELECT
    TOP 1 parentId,
    name,
    (
        CASE
            WHEN name = 'CatA.2' THEN 1
            ELSE 0
    ) sortOrder
FROM
    catagories
WHERE
    parentId = 1
ORDER BY
    sortOrder 
DESC

Now the issue with my query is that it doesn't cover all the scenarios.

Can anyone help me with the query?

Answer 1

Constraints should be embedded in the table definition if possible, eg

create table Categories
(
  Id int primary key,
  ParentId int null references Categories,
  Name varchar(20) not null,
  constraint uk_Name_Sibling unique (ParentId,Name)
)