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:

    | 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?



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.

  • Can you explain what you mean with "get the SET settings correct"? Do I need to do anything special in order to activate filtered indexes? – a_horse_with_no_name Dec 5 '12 at 7:13
  • 2
    @a_horse_with_no_name See "Required SET Options for Filtered Indexes" in the CREATE INDEX (Transact-SQL) documentation. – Paul White Dec 5 '12 at 8:37
  • Good answer, although it could get a bit cluttered because www and index.html were only examples. So if I could theoretically end up with thousands or tens of thousands of filtered indexes. But I will use this with a trigger on insert and update to force uniqueness. – Alex Erwin Dec 6 '12 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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