Take the 2-minute tour ×
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.

With reference to this SO question, what would be a correct way to implement Unique tag Combination Constraint in SQL Server 2005?

To illustrate the problem, I make an example:

A tag_bundle consists of one or more than one tags. A unique tag combination can map to a unique tag_bundle, vice versa.

 tag_bundle                   tag            tag_bundle_relation
 +---------------+        +--------+      +---------------+--------+
 | tag_bundle_id |        | tag_id |      | tag_bundle_id | tag_id |
 +---------------+        +--------+      +---------------+--------+
 |       1       |        | 100    |      |       1       |  100   |
 +---------------+        +--------+      +---------------+--------+
 |       2       |        | 101    |      |       1       |  101   |
 +---------------+        +--------+      +---------------+--------+ 
                          | 102    |      |       2       |  101   |
                          +--------+      +---------------+--------+  
                                          |       2       |  102   |
                                          +---------------+--------+

There can't be another tag_bundle having exactly the same combination from tag 100 and tag 101. There can't be another tag_bundle having exactly the same combination from tag 101 and tag 102.

How can I ensure such unique constraint when executing SQL "concurrently"!! that is, to prevent concurrently adding two bundles with exactly the same tag combination

Adding a simple unique constraint on any table does not work, Is there any solution other than Trigger or explicit lock.

I come to only this simple way: make tag combination into string, and let it be a unique column.

tag_bundle  (unique on tags)         tag            tag_bundle_relation
 +---------------+-----------+      +--------+      +---------------+--------+
 | tag_bundle_id |  tags     |      | tag_id |      | tag_bundle_id | tag_id |
 +---------------+-----------+      +--------+      +---------------+--------+
 |       1       | "100,101" |      | 101    |      |       1       |  101   |
 +---------------+-----------+      +--------+      +---------------+--------+
                                    | 100    |      |       1       |  100   |
                                    +--------+      +---------------+--------+ 

but it seems not a good way :(

Is there no other solution but with triggers?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

My only suggestion is to create stored procedure that performs DML on tag_bundle_relation and restrict all users from manipulating the table directly (it's in a sense 'explicit lock' as you say). I don't see any other acceptable ways to enforce the required restriction. In my understanding your simple solution just adds complexity to the system - you will need more triggers to synchronize tags field with tag_bundle_relation and tag tables. Also, you may get false fail if you are adding records to tag_bundle_relation one by one - for instance, inserting tag_bundle_id=3 with tag_ids 100,101,102 will fail after attempting to insert 101 (assuming 100 already inserted).

share|improve this answer
    
Thanks for the answer, How would you ensure Unique Sets of Tags for each Tag_Bundle_Id in the Stored Procedure? Thanks, –  mxasim Aug 9 '12 at 15:23
1  
@mxasim : I'd probably write a stored procedure that accepts table parameter create type TP_tag_bundle_param as table(tag_bundle_id int not null, tag_id int not null, primary key (tag_bundle_id, tag_id)), then in the body I need to make sure I don't have exactly the same tag_id s associated with another bundle (various methods, using join or exist/not exist). That's just a general approach I'd take; it's definitely needs refining and more thinking. –  a1ex07 Aug 9 '12 at 15:44
    
Thanks for you help –  mxasim Aug 9 '12 at 16:37
add comment

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.