4

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?

1 Answer 1

4

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).

2
  • 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, 2012 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, 2012 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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