How to realize uniqueness where the order of the fields does not matter

Question

I think the following example will best explain the situation. Say we have the following table structure:

------------------------------------- Member1 int NOT NULL (FK)(PK) Member2 int NOT NULL (FK)(PK) ------------------------------------- Statust char(1) NOT NULL 

Here is the contents of the table for the table:

 Member1 Member2 Status ---------------------------- 100 105 A 

My question is how to implement uniqueness so that the next INSERT FAIL statement is based on that single row that is already listed in the table.

 INSERT status_table (Member1,Member2,Status) VALUES(105,100,'D'); 

Basically, I'm trying to model the relationship between two members. The Status field is the same as whether we have (100,105) or (105,100).

I know that I could use the before_insert and before_update trigger to check the contents in a table. But I was wondering if there is a better way to do this ... If my database model is different ...

Answer 1

If you can make sure that all applications / users store the member IDs in the least order (smallest MemberID in Member1 and largest in Member2 ), then you can simply add a check constraint:

 ALTER TABLE Status_table ADD CONSTRAINT Status_table_Prevent_double_pairs CHECK (Member1 < Member2) 

If you don’t want to do this or want to save additional information (because you store (just an example) that "member 100 is invited (liked, killed, any) member 150", and not vice versa), then you can use the @Tegiri approach, slightly modified (multiplying two sufficiently large integers will be an overflow problem):

 CREATE TABLE Status_table ( Member1 INT NOT NULL , Member2 INT NOT NULL , Status CHAR(1) NOT NULL , MemberOne AS CASE WHEN Member1 < Member2 THEN Member1 ELSE Member2 END --- a computed column , MemberTwo AS CASE WHEN Member1 < Member2 THEN Member2 ELSE Member1 END --- and another one , PRIMARY KEY (Member1, Member2) , UNIQUE (MemberOne, MemberTwo) , ... --- FOREIGN KEY details, etc ) ; 

Answer 2

The database model error is because you have two {Member1, Member2} objects, which, saying that it does not matter, that, you say, is the same {Member} object. In other words, you have one fact in two places, one of the cardinal sins of relational database design.

A high-level solution would better model the nature of the relationship. An example would be a marriage of two people. Instead of “Brides and Groom being married” and the fussiness that is first listed, you have “Marriage #xyz” between (contains) participants A and B. "So, the Marriage table with the primary key, the MarriageMember table with the foreign key for marriage, a foreign key for "Person" and a primary key in both columns.Lets you have more than two members, which can be useful if you talk about Heinline.

If you are stuck with existing circuits (and arent all of us), Id requires the data to be presented with, say, the lowest value indicated first, so that they are always ordered properly. You could do checksums in two columns as a computed column, but that would not guarantee uniqueness. But, alas, at the end of the day, your model is apparently too weak for your purposes.

---

additions

In accordance with the comments below, if you are modeling the participants this member is associated with, then you have the situation “The participant is connected with other members”. Here Member1 is the "primary" member, and Member2 is the other member to which the "this" member is associated. (And this is the distinction needed between two Member columns). Thus, if the relationship is bidirectional, then you will need two entries to cover both “Member A is associated with Member B” and “Member B” is associated with Member A. ”This, of course, will be forcibly entered with the primary key in {Member1, Member2}, since Status seems to be irrelevant (there is only one relationship, and not several, based on status).

Answer 3

One way to avoid the trigger is to try to compute UNIQUE columns on Member1 and Member2:

 create table test (Member1 int not null, Member2 int not null, Status char(1) , bc as abs(binary_checksum(Member1))+abs(binary_checksum(Member2)) PERSISTED UNIQUE) INSERT INTO test values(123, 456, 'A'); --succeeds INSERT INTO test values(123, 789, 'B'); --succeeds INSERT INTO test values(456, 123, 'D'); --fails with the following error: --Msg 2627, Level 14, State 1, Line 1 --Violation of UNIQUE KEY constraint 'UQ__test__3213B1084A8F946C'. Cannot insert duplicate key in object 'dbo.test' 

Answer 4

Here is an alternative way to take a look at this. In fact, you can apply the rule that the mutual relation is always expressed by the presence of two lines (A, B) and (B, A) instead of one.

 CREATE TABLE MutualRelationship (Member1 INT NOT NULL, Member2 INT NOT NULL, Status CHAR(1), PRIMARY KEY (Member1, Member2), UNIQUE (Member1, Member2, Status), FOREIGN KEY (Member2, Member1, Status) REFERENCES MutualRelationship (Member1, Member2, Status)); INSERT INTO MutualRelationship (Member1, Member2, Status) VALUES (100,105,'A'), (105,100,'A'); 

Answer 5

It is impossible to imagine a better way to increase an existing unique constraint than a trigger. eg.

 CREATE TRIGGER dbo.StatusTable_PreventDualUniques ON dbo.status_table INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; IF EXISTS ( SELECT 1 FROM inserted AS i INNER JOIN dbo.status_table AS s ON i.Member1 = s.Member1 AND i.Member2 = s.Member2 OR i.Member2 = s.Member1 AND i.Member1 = s.Member2 ) BEGIN RAISERROR('Duplicate detected', 11, 1); END ELSE BEGIN INSERT dbo.status_table(Member1, Member2, Status) SELECT Member1, Member2, Status FROM inserted; END END 

Now, from my head, this applies only to single-line inserts. The logic can be a little complicated if you need to handle multi-line inserts, since you need to check for duplicates both inside inserted and between inserted and the base table. It also does not handle the high level of concurrency at the isolation level by default (for example, another transaction inserts a repeating row between validation and insertion). But that should be the beginning.

(You will also need one for UPDATE ...)

Answer 6

Here you can find an excerpt from Symmetric Functions in SQL Design Models.

Consider a box inventory database

 table Boxes ( length integer, width integer, height integer ) 

Box sizes in the real world, however, are usually not given in any particular order. The choice of which dimensions occupy the length, width and height is essentially arbitrary. What if we want to determine the boxes according to their size? For example, we would like to say that a field with length = 1, width = 2 and height = 3 is the same field as a field with length = 3, width = 1 and height = 2. In addition, what about declaring a unique size limits? More specifically, we will not allow the two boxes to be the same size.

The analytical mind could not understand that the essence of the problem is the ordering of the columns. The column values ​​for length, width, and height can be used interchangeably to form another legal record! Therefore, why do not we introduce 3 pseudo columns, say, A, B and C such that

 A ≤ B ≤ C 

Then the only restriction on A, B, C must satisfy our requirement! It can be implemented as a unique unique index, if we can express A, B, C analytically in length, width and height. A piece of cake: A - the greatest length, width, height; C is the smallest of them, but how do we express B? Well, the answer is easy to write

 B = least (greatest (length,width), greatest (width,height), greatest (height,length) ) 

although it’s hard to explain.

The mathematical perspective, as usual, explains a lot. Consider the cubic equation

If we know the roots x1, x2, x3, then the cubic polynomial could be factorized, so that we have

Having married both equations, we express the coefficients a, b, c through the roots x1, x2, x3

Figure 4.1: The shape of the graph of the polynomial y=(x-x1)(x-x2)(x-x3) completely determined by the roots x1, x2 and x3. Their exchange does not affect anything.

The functions -x1-x2-x3, x1x2+x2x3+x3x1, -x1x2x3 are symmetric. The permutation x1, x2, x3 does not affect the values ​​of a, b, c. In other words, the order among the roots of the cubic equation does not matter: formally, we are talking about a lot of roots, not a list of roots. This is exactly the effect that we want in our example using Boxes. Symmetric functions rewritten in length, width, height,

 length+width+height length*width+width*height+height*length length*width*height 

These expressions have been slightly simplified due to the fact that the negation of a symmetric function is also symmetric.

Our last solution is strikingly similar to the previous one, where the largest operator plays the role of multiplication, and the minor operator as an addition. You can even propose a solution that is a mixture between two

 least(length,width,height) least(length+width,width+height,height+length) length+width+height 

The reader can verify that these three functions are symmetrical again2. The last step is to write our solution in formal SQL

 table Boxes ( length integer, width integer, height integer ); create unique index b_idx on Boxes( length + width + height, length * width + width * height + height * length, length * width * height ); 

Symmetric functions provide the foundation for a great solution. In practice, however, the problem can often be solved by reorganizing the circuit. In the example of the box inventory database, we don’t even need to reorganize the scheme: we can simply demand to change the practice of inserting unbuilt records (length,width,height) and require that

 length ≥ width ≥ height 

Answer 7

A slight departure from @ypercube's solution will be to create an indexed view and move the unique constraint to the view. Here's the full script that demonstrates the approach:

 /* the reference table (almost irrelevant for the tests, but added to make the environment closer to the one in the question) */ CREATE TABLE dbo.Members ( ID int IDENTITY CONSTRAINT PK_Members PRIMARY KEY, Name varchar(50) ); GO /* the table to add the constraint on */ CREATE TABLE dbo.Data ( Member1 int CONSTRAINT FK_Data_Member1 FOREIGN KEY REFERENCES dbo.Members (ID), Member2 int CONSTRAINT FK_Data_Member2 FOREIGN KEY REFERENCES dbo.Members (ID), Statust char(1), CONSTRAINT PK_Data PRIMARY KEY (Member1, Member2) ); GO /* the indexed view that the constraint will actually be applied to */ CREATE VIEW dbo.DataView WITH SCHEMABINDING /* required with indexed views */ AS SELECT /* the column definitions are practically identical to ypercube */ Member1 = CASE WHEN Member1 > Member2 THEN Member2 ELSE Member1 END, Member2 = CASE WHEN Member1 > Member2 THEN Member1 ELSE Member2 END FROM dbo.Data GO /* finally, the constraint itself */ CREATE UNIQUE CLUSTERED INDEX UQ_DataView ON dbo.DataView (Member1, Member2); GO /* preparing the stage: adding some data to the reference table */ INSERT INTO dbo.Members (Name) SELECT 'Member A' UNION ALL SELECT 'Member B' UNION ALL SELECT 'Member C'; GO /* the first two rows should and do insert into the target table without issues */ INSERT INTO dbo.Data (Member1, Member2, Statust) VALUES (3, 1, 'A'); INSERT INTO dbo.Data (Member1, Member2, Statust) VALUES (2, 3, 'A'); GO /* and this one fails, which demonstrates the constraint in work */ INSERT INTO dbo.Data (Member1, Member2, Statust) VALUES (1, 3, 'B'); GO /* cleaning up */ DROP VIEW dbo.DataView; DROP TABLE dbo.Data; DROP TABLE dbo.Members; 

More about indexed views on MSDN:

• Designing Indexed Views

• Improving Performance with SQL Server 2008 Indexed Views

Answer 8

Instead of trying to get this table to apply this particular business logic, would it be better if it were encapsulated in a stored procedure? You certainly get more flexibility in securing a unique relationship between two members.