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:
我的数据库中有一个表,其中记录在概念上可以是其他rcords的子项。该表具有非null名称字段。我需要确保一组子项中的每个名称都是唯一的,但不是在整个数据库中。我想使用数据库中的约束强制执行此操作。实现这一目标的最佳方法是什么?我知道我将不得不在这个过程中的某个点进行查询,如下所示:
@NameParameter NVARCHAR(512)
Select Name from MyTable
WHERE Name=@NameParameter
The question is where do I put this query?
问题是我在哪里提出这个查询?
2 个解决方案
#1
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.
我可能没有正确理解你的问题,但我的建议是创建另一个引用孩子父记录的列。然后,您可以基于这两列创建A多列索引,以加快在where子句中引用这些列的任何查询...因此,您的唯一性将来自{parent_name,child_name}。对这两列的约束将充当该表的键,并且不允许重复。
@childname NVARCHAR(255), @parentname NVARCHAR(255)
SELECT * FROM [child_records]
WHERE [parent_name] = @parentname
AND [child_name] = @childname
#2
At first glance I believe this should go into a "Instead of trigger". This link provides a good example.
乍一看,我认为这应该进入一个“而不是触发器”。此链接提供了一个很好的示例。
#1
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.
我可能没有正确理解你的问题,但我的建议是创建另一个引用孩子父记录的列。然后,您可以基于这两列创建A多列索引,以加快在where子句中引用这些列的任何查询...因此,您的唯一性将来自{parent_name,child_name}。对这两列的约束将充当该表的键,并且不允许重复。
@childname NVARCHAR(255), @parentname NVARCHAR(255)
SELECT * FROM [child_records]
WHERE [parent_name] = @parentname
AND [child_name] = @childname
#2
At first glance I believe this should go into a "Instead of trigger". This link provides a good example.
乍一看,我认为这应该进入一个“而不是触发器”。此链接提供了一个很好的示例。