How To Insert Unique Composite ID For Hierarchical Table (SQL Server)

Question

Hi, I have this table structured as follows :

PK     ParentPK     ID
1      Null         A
2      1            B
3      2            C
4      1            D
5      4            E

For the table above, I already have a function that can generate the following results :

PK     FullID
3      ABC
5      ADE

This function takes rows that don't have children, and recursively generate FullID for each row up to their parents.

My application expects users to enter a string of FullID, such as "BEF", "ABD", and press Save button and then its the application duty to internally parse it to a suitable hierarchy structure (that will consists of multiple rows) and insert them to the table.

The question is, how can the application guarantee the uniqueness of FullID? I need to be able to reject duplicate FullID entered by users.

I understand that I can check the FullID first using my function, then if it isn't exist, I can perform the inserts. But if these steps performed in a non-atomic operation, duplicate FullID may be entered i a heavy traffic usage.

Should I wrap this in a stored procedure? using Begin/Commit Transaction block? or is there any better way to do it? Thanks a lot.

Answer 1

So you are looking for something like this

BEGIN TRAN
if not exists (select a.PK from GetFullID(a.PK))
begin
    --do operation--
end
commit TRAN

Answer 2

The fact that you're facing questions like this is a strong code smell that your data model is flawed. If you need unique constrained, then they should be enforced by an unique constraint. In other words, the 'BEF', 'ABD' full ID should be explicitly persisted in storage somewhere and uniqueness enforced on the full id column with an ordinary unique index.