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, Aug 09 '21 at 07: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, Aug 09 '21 at 07: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, Aug 09 '21 at 09:30
I'm lost. Your sample query is referring to the same table twice, so if the `where` clause passes, it is going to insert duplicates. That seems highly suspicious. — Gordon Linoff, Aug 09 '21 at 11:37
@GordonLinoff you are right, the query is not doing what I expected. It's just a sample for a conditional insert that I took form stackoverflow and tried to adapt to my case. — Nils Rommelfanger, Aug 10 '21 at 10:57
@Charlieface I can imagine modeling the hierarchy but how would that help with the constraint? I would still need a lock and some procedure to check for conflicts, right? — Nils Rommelfanger, Aug 10 '21 at 11:02
You can just check for inserting the same key with the same parent — Charlieface, Aug 10 '21 at 11:02
@Charlieface That is the same problem as with a simple unique constraint. The existing entry and the one that I want to insert are not necessarily the same. If com.foo exists it has a different parent than com.foo.bar that I want to insert. Yet com.foo.bar must not be inserted. — Nils Rommelfanger, Aug 10 '21 at 11:06
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, Aug 10 '21 at 11:58

score 1 · Accepted Answer · answered Aug 09 '21 at 11:40

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.

MSSQL Namespace Constraint

1 Answers1