SQL constraint to have one unique value in two columns

I need to assure that a value is unique in two columns (this is not a "two columns" index issue).

Table A
Column A1       Column A2

Memphis         New York     -> ok
San Francisco   Miami        -> ok
Washington      Chicago      -> ok
Miami           Las Vegas    开发者_Python百科-> Forbidden ! Miami already exists 

Is it possible ?

My example is with cities but don't focalize on that. My real need is about generated hexadecimal ids.

In SQL Server it is possible to enforce the uniqueness with the help of an indexed view. You will also need a numbers table (if you haven't already got one) in the same database as your Table A.

Here's my testing script with some comments:

CREATE TABLE MyNumbersTable (Value int);
-- You need at least 2 rows, by the number of columns
-- you are going to implement uniqueness on
INSERT INTO MyNumbersTable
SELECT 1 UNION ALL
SELECT 2;
GO
CREATE TABLE MyUniqueCities (  -- the main table
  ID int IDENTITY,
  City1 varchar(50) NOT NULL,
  City2 varchar(50) NOT NULL
);
GO
CREATE VIEW MyIndexedView
WITH SCHEMABINDING  -- this is required for creating an indexed view
AS
SELECT
  City = CASE t.Value    -- after supplying the numbers table
    WHEN 1 THEN u.City1  -- with the necessary number of rows
    WHEN 2 THEN u.City2  -- you can extend this CASE expression
  END                    -- to add more columns to the constraint
FROM dbo.MyUniqueCities u
  INNER JOIN dbo.MyNumbersTable t
    ON t.Value BETWEEN 1 AND 2  -- change here too for more columns
GO
-- the first index on an indexed view *must* be unique,
-- which suits us perfectly
CREATE UNIQUE CLUSTERED INDEX UIX_MyIndexedView ON MyIndexedView (City)
GO
-- the first two rows insert fine
INSERT INTO MyUniqueCities VALUES ('London', 'New York');
INSERT INTO MyUniqueCities VALUES ('Amsterdam', 'Prague');
-- the following insert produces an error, because of 'London'
INSERT INTO MyUniqueCities VALUES ('Melbourne', 'London');
GO
DROP VIEW MyIndexedView
DROP TABLE MyUniqueCities
DROP TABLE MyNumbersTable
GO

Useful reading:

• Creating Indexed Views

• The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

You need to add a constraint trigger that looks it up after insert/update.