sql unique constraint on a 2 columns combination

by

If your database allows expressions in an index you can do something like this (ANSI SQL):

CREATE UNIQUE INDEX on your_table (least(column1, column2)
                              , greatest(column1, column2));

Note this is a unique index not a unique constraint. The only difference for most DBMS is that you can’t have a unique index as the target of a foreign key, but otherwise they serve the same purpose.

If your DBMS does not have least() or greatest() you can replace that using a CASE expression:

create unique index on your_table 
  (case column1 < column2 then column1 else column2 end, 
   case column2 > column1 then column2 else column1 end));

More Related Contents:

  1. How does database indexing work? [closed]
  2. How to create a unique index on a NULL column?
  3. What is a Covered Index?
  4. NOT NULL constraint over a set of columns
  5. What is the purpose of constraint naming
  6. Deferrable Constraints in SQL Server
  7. Continuing a transaction after primary key violation error
  8. Two single-column indexes vs one two-column index in MySQL?
  9. VARCHAR as foreign key/primary key in database good or bad?
  10. Insert into … values ( SELECT … FROM … )
  11. Refactor a PL/pgSQL function to return the output of various SELECT queries
  12. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  13. IN vs OR in the SQL WHERE Clause
  14. Where is |DataDirectory| defined?
  15. MySQL not using indexes with WHERE IN clause?
  16. When to use SQL Table Alias
  17. why is null not equal to null false
  18. List columns with indexes in PostgreSQL
  19. Does DB2 have an “insert or update” statement?
  20. Max length for client ip address [duplicate]
  21. Query that ignore the spaces
  22. How to write a constraint concerning a max number of rows in postgresql?
  23. Postgres constraint for unique datetime range
  24. Android multiple databases open
  25. Is there a version control system for database structure changes?
  26. Using a database table as a queue
  27. What does ON [PRIMARY] mean?
  28. How do you interpret a query’s explain plan?
  29. Oracle DB: How can I write query ignoring case?
  30. SQL Server equivalent of WM_CONCAT function [duplicate]

Leave a Comment