Complex queries in SQL Server 2005 database

Question

I have a table in my database in which records conceptually can be children of other rcords. The table has a non-null name field. I need to ensure that each name in a set of children is unique, but not across the entire database. I'd like to enforce this using a constraint inside the Database. What's the best way to accomplish this? I know that I am going to have to do a query at some point in the process like this:

@NameParameter NVARCHAR(512)
Select Name from MyTable
WHERE Name=@NameParameter

The question is where do I put this query?

Answer 1

At first glance I believe this should go into a "Instead of trigger". This link provides a good example.

Answer 2

I may not be understanding your question correctly, but my suggestion is to create another column that references the parent record of the child. You could then create a A multiple-column index based on those two columns to speed up any queries that reference these columns together in a where clause... Thus your uniqueness would come from {parent_name, child_name}. A constraint on these two columns would act as a key for that table, and not allow duplicates.

@childname NVARCHAR(255), @parentname NVARCHAR(255)
SELECT * FROM [child_records] 
WHERE [parent_name] = @parentname 
AND [child_name] = @childname