Sql-server – I need help using hierarchyid datatype and requiring a unique constraint on a varchar field

constrainthierarchysql serversql-server-2008

I want to resolve a bunch of tables that had parent->child->grandchild relationships into a single table using hierarchyid identifiers. Here is what I came up with based on what I had. 

    CREATE TABLE [dbo].[tbl_server_group]
    (
        [refid]     [int] IDENTITY(1,1) NOT NULL,
        [refhid]    [hierarchyid] NOT NULL,
        [reflvl]    AS refhid.GetLevel() PERSISTED,
        [refdate]   [datetime] NOT NULL DEFAULT GETDATE(),
        [refname]   [varchar](50) NOT NULL
    )

My issue is that I need to be able to require that the refname is unique but only within the container that it is in. Being that I am using this to represent servers, I wanted to emulate a DNS tree of sorts but add other layers to it as well. My example being the following:

    ROOT
    | something.com
    || www
    ||| index.html
    || staging
    ||| testbed.php
    | someotherthing.com
    || test
    ||| index.html

I want to make sure that something.com only has one "www" and that www only has one "index.html".

Is there anyway to do this with constraints? If not, is there an efficient way to force uniqueness?

Thanks

Best Answer

If you add another column for the ancestor, e.g.

CREATE TABLE [dbo].[tbl_server_group]
(
    [refid]     [int] IDENTITY(1,1) NOT NULL,
    [refhid]    [hierarchyid] NOT NULL,
    [reflvl]    AS refhid.GetLevel() PERSISTED,
    [refdate]   [datetime] NOT NULL DEFAULT GETDATE(),
    [refname]   [varchar](50) NOT NULL,
    [reflvl1id] AS refhid.GetAncestor(1) PERSISTED
);

You can ensure that there is only ever one 'www' per parent. You can create a similar index for 'index.html'.

create unique index ux_tbl_server_group_1 on tbl_server_group(reflvl1id, refname)
where refname = 'www';

Obviously this is carrying it further than just the first level (www) and for children of www (index.html) but I don't see why the expanded restrictions won't work.
If you had to implement it exactly as specified, then you will have to resort to using a trigger.

Note: This makes use of SQL Server's filtered index, so you have to get the SET settings correct for any process that touches this table.

Related Question