1

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?

  • When you say, "... while other brand values can exist many times." do you mean that in the table, you could see ProductOne more than one time? – Antoine Hernandez Mar 8 '16 at 1:13
  • @SoleDBAGuy yes. – jsp0407 Mar 8 '16 at 1:15
  • 1
    An example of what you mean (with DDL and DML) would be great. I'm not quite grasping your exact meaning. – Vérace Mar 8 '16 at 5:09
  • So in column1, you want to allow "unknown" only to exist once, but you might want "product2" , "product3" and others to be duplicated? You could do it with a check constraint but I'd be worried about performance. – Phil Sumner Mar 8 '16 at 9:51
2

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) ;
  • Thank you so much for your detailed answer, you have proven my instinct that it is the right place I should ask this question. You guys are really amazing. But can you please add some references or examples? It's for others who wants to check if it is working just like what you have stated. Anyway, I will choose your answer, it's enough for me. Thanks. – jsp0407 Mar 9 '16 at 1:09
2

Some DBMSes support indexes with a WHERE-condition

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.