Sql database engine that support “specific value duplicate restriction”

database-engine

For example, in the brand column of a product database, the brand value "unknown" should only exist once while other brand values can exist many times.

Is there a sql database engine that supports it? or should I do it in application layer?

Best Answer

SQL Server and PostgreSQL support partial (filtered) indexes that can be defined (as in dnoeth's answer) and can be UNIQUE:

CREATE UNIQUE INDEX OnlyOneUnkownBrand
ON product (brand)
WHERE brand = 'unknown' ;

In Oracle, you can achieve similar functionality with an index on a expression:

CREATE UNIQUE INDEX OnlyOneUnkownBrand
ON product (CASE WHEN brand = 'unknown' THEN brand END) ;

For rows that have different brand, the value of the expression is NULL. In older versions of Oracle, null values are not even stored in single-column indexes, so you have a partial index!

In DB2 (versions LUV 10.5 and later), the syntax is almost identical to Oracle but we can exclude the NULL values, so the index actually stores only the values we need :

CREATE UNIQUE INDEX OnlyOneUnkownBrand
ON product (CASE WHEN brand = 'unknown' THEN brand END) 
EXCLUDE NULL KEYS;

MySQL does not support partial indexes but supports computed columns (MariaDB since version 5.3 and MySQL only recently in 5.7). Using that one can emulate a partial index:

ALTER TABLE product
   ADD COLUMN UnknownBrand AS 
       (CASE WHEN brand = 'unknown' THEN brand END) PERSISTED,
   ADD UNIQUE INDEX OnlyOneUnkownBrand
       (UnknownBrand) ;
Related Question