How do I ensure a relationship is commutative and unique?

by Evan Carroll   Last Updated January 13, 2018 00:06 AM

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?



Answers 1


@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.

Code,

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_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option 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,

When SET QUOTED_IDENTIFIER is 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_IDENTIFIER is OFF for connections from DB-Library applications.

I'm guessing 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.

Evan Carroll
Evan Carroll
January 12, 2018 23:28 PM

Related Questions


computed columns vs views

Updated April 10, 2017 13:06 PM

Computed Column Index Not Used

Updated January 10, 2018 18:06 PM

computed column making the select query to run slow

Updated February 18, 2017 07:06 AM


When are computed columns computed?

Updated April 27, 2016 09:02 AM