Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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

share|improve this question

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
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
@SQLKiwi: thanks. – a_horse_with_no_name Dec 5 '12 at 15:23
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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