An opposite constraint

This article discusses an issue raised in the thread:

microsoft.public.sqlserver.programming
Sunday, June 29, 2008 
'Storing a collection of lines'
http://tinyurl.com/56dpd4

Give two columns in a table suppose you want to eliminate the opposite
data where there's no mathematical or logical relationship between
the columns. For example, consider a trip between two cities. It's 
equally likely a trip could start and end in either direction. If the 
table already has:

column A  column B
--------  --------
NEW YORK  CHICAGO

We want to prevent the opposite from being entered:

column A  column B
--------  --------
CHICAGO   NEW YORK  

If the table has:

column A  column B
--------  --------
CHICAGO   NEW YORK  

We want to prevent the opposite from being entered:

column A  column B
--------  --------
NEW YORK  CHICAGO

Because there's no relationship between the columns an sql check
constraint can't be used. But in Dataphor a simple transition
constraint can be used. The Opposite constraint simply checks if 
the opposite data for columns A and B already exists in the table. 
If it does the current row is rejected. The constraint works the
same way for a single insert as it does for inserting multiple rows
(a table). 
For example:

create session table MyTable
{ A:String,B:String,C:String,key{A,B} };

alter table MyTable
{
  create transition constraint Opposite
   on insert //The current (row) values for columns A and B are accessed 
             //by prefixing each with 'new', ie. new.A, new.B . 
    not exists (MyTable {A X,B Y} {Y A,X B} where A=new.A and B=new.B)  
 tags        //A custom error message can be written using the current 
             //row values (new.A, new.B).
 { DAE.Message =
   "'For A: ' + new.A + ' and B: '+ new.B + ' there is a opposite, A: ' + new.B + ' and B: ' + new.A " 
 }   
};

These insert succeed:

insert row{'NEW YORK' A,'CHICAGO' B,'1c' C} into MyTable;
insert row{'CALIFORNIA' B,'TEXAS' A,'1d' C} into MyTable;

Inserting an opposite will fail and the custom error message will be raised:

insert row{'NEW YORK' B,'CHICAGO' A,'1e' C} into MyTable;
"For A: CHICAGO and B: NEW YORK there is a opposite, A: NEW YORK and B: CHICAGO"

insert row{'CALIFORNIA' A,'TEXAS' B,'1d' C} into MyTable;
"For A: CALIFORNIA and B: TEXAS there is a opposite, A: TEXAS and B: CALIFORNIA"

Given that the table contains the data "NEW YORK" (A) and "CHICAGO" (B), 
inserting the following rows as a table will fail:

insert table{
             row{'DENVER' A,'BOSTON' B,'1c' C},
             row{'RENO' B,'MIAMI' A,'1d' C},
             row{'CHICAGO' A,'NEW YORK' B,'1e' C} 
            } into MyTable;
       
"For A: CHICAGO and B: NEW YORK there is a opposite, A: NEW YORK and B: CHICAGO. 

Note that the primary key constraint will eliminate the same A and B cities from
being entered twice but entering the opposite cities does not violate it. That's
what the Opposite constraint is for. The Opposite constraint is much simpler than
an sql solution using triggers.