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?