In PostgreSQL, we can ensure the relations stored inside a table a commutative-ly (or bidirectional etc) unique. For instance,
CREATE TABLE foo ( a int, b int, UNIQUE ( greatest(a,b), least(a,b) ) ); INSERT INTO foo VALUES (1,2); -- works INSERT INTO foo VALUES (2,1); -- explodes
I know SQL Server can create computed columns, and using that I thought I could build what I want..
CREATE TABLE f ( a int, b int, g AS (CASE WHEN a>b THEN a ELSE b END), l AS (CASE WHEN a>b THEN b ELSE a END), UNIQUE(g,l) );
But I get, Msg 1934, Level 16, State 1, Server x230, Line 1
CREATE TABLE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Followed by, Msg 1750, Level 16, State 1, Server x230, Line 1
Could not create constraint or index. See previous errors.
So I guess that this can not be done with a UNIQUE
index. Is this currently possible at all?