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?
@Dan Guzman answered it in the comments,
The error simply means the quoted identifier session setting is off, which must be on for computed column indexes. Try adding
SET QUOTED_IDENTIFIER ON;before the `CREATE TABLE' script.
SET QUOTED_IDENTIFIER ON; 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) );
From the docs on quoted identifiers
SET QUOTED_IDENTIFIERmust be ON when you are creating or changing indexes on computed columns or indexed views. If
DELETEstatements on tables with indexes on computed columns or indexed views will fail. For more information about required
SEToption settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).
Interestingly, it's not the default. Despite what they say. They lie. Their official client,
sqlcmd requires the flag
-I Enable Quoted Identifiers,
ON(default), all strings delimited by double quotation marks are interpreted as object identifiers. [...] This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for
SET QUOTED_IDENTIFIERis OFF for connections from DB-Library applications.
sqlcmd is a "DB-Library application", and what Microsoft means to say is the default gets default-changed by the default in their own client. That's pretty much inane gobbledygook.