MSSQL Namespace Constraint

Question

On a SQL Server I have a table namespace

Namespace
---------
Prefix
com.foo
com.bar
com.foobar

And I want to prevent inserts if a conflicting namespace already exists. A namespace includes all sub namespace. E.g. com.foo includes everything 'below' com.foo e.g. com.foo.bar and com.foo.bar.baz but not com.foobar or com.foobar.baz (com.foobar is a different namespace than com.foo)

The check should be atomic to prevent concurrency issues.

I have this insert that does the insert conditionally. But I am not sure if it is atomic.

INSERT INTO namespace
(PREFIX)
SELECT t1.PREFIX
FROM namespace t1
WHERE NOT EXISTS(SELECT t2.PREFIX
                 FROM namespace t2
                 WHERE t2.PREFIX in ('com.foo.bar','com.foo','com'))

The comment here https://stackoverflow.com/a/16636779/1844551 suggests that with Postgres it is not guaranteed to be atomic. How about MSSQL?

Ideally this would be a check constraint, but I don't know how to construct the where t2.PREFIX in ('com.foo.bar','com.foo','com') from an input like com.foo.bar.

Note: a simple startswith doesn't do the trick since com.foo and com.foobar are different namespaces that don't conflict each other. So com.foobar can be inserted if com.foo exists.

So my current idea is to implement this query in the client code and check the result if something was inserted.

Is there a better way to implement this in the DB as a constraint?

Not enough. com.foo.bar should not be inserted if com.foo exists. — Nils Rommelfanger
– Nils Rommelfanger, Commented Aug 9, 2021 at 7:32
It seems like you're working with data that describes a hierarchy. It would probably be better to model the hierarchy within the database rather than having to (re-)construct it from a string. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 9, 2021 at 7:50
Looks like you need a self-referencing foreign key. But for your query above: HOLDLOCK hint or SERIALIZABLE isolation level will guarantee atomicity — Charlieface
– Charlieface, Commented Aug 9, 2021 at 9:30
You could model it so that you always insert the upper levels also, with a DoesInherit bit flag saying whether it includes child namespaces. Then when you insert a child namespace, you generate all the necessary nodes, and check against any existing parent nodes with DoesInherit = 1 — Charlieface
– Charlieface, Commented Aug 10, 2021 at 11:58

Gordon Linoff · Accepted Answer · 2021-08-09 11:40:40Z

1

If you wanted to check for conflicts with this insert:

INSERT INTO namespace (PREFIX)
    SELECT v.PREFIX
    FROM (VALUES ('com.foo.bar')) v(prefix);

You can use:

INSERT INTO namespace (PREFIX)
    SELECT v.PREFIX
    FROM (VALUES ('com.foo.bar')) v(prefix)
    WHERE NOT EXISTS (SELECT 1
                      FROM namespace n
                      WHERE v.prefix LIKE n.prefix + '.%'
                     );

I cannot think of a simple way to implement this logic as a constraint. That leaves basically three options:

Use manual coding as above, perhaps embedded in a stored procedure.
Use a trigger.
Use a user-defined function with a check constraint.

answered Aug 9, 2021 at 11:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MSSQL Namespace Constraint

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related