0

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?

1

1 Answer 1

0

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)
)
2
  • I cannot change the table structure, plus name cannot be unique for other categories, it s only unique on siblings level Commented Jan 22, 2023 at 17:28
  • See updated answer Commented Jan 22, 2023 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.