Mysql unique constraint allowing single row for a combination

Question

Is it possible to have a unique constraint such that one particular column has a value only once?

For instance

-----------------------    
name | price | default
-----------------------
XYZ  |  20   | TRUE
-----------------------
XYZ  |  30   | FALSE
-----------------------
XYZ  |  40   | FALSE
-----------------------
ABC  | 50    | FALSE
-----------------------

So in above table, for a particular name value, default value can be TRUE only once. And will have a unique constraint on name & price columns.

Is this possible?

Answer 1

A normal way to do this is to extract a separate table to hold the default price :

CREATE TABLE price (
    name VARCHAR(255),
    price INT,
    PRIMARY KEY (name, price)
) ;

CREATE TABLE defaultPrice (
    name VARCHAR(255),
    price INT,
    PRIMARY KEY (name),
    FOREIGN KEY(name, price) REFERENCES price(name, price)
);

Most people will advise introducing surrogate keys:

CREATE TABLE item (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    UNIQUE(name)
);

CREATE TABLE price (
    itemId INT,
    price INT,
    PRIMARY KEY (itemId, price),
    FOREIGN KEY (itemId) REFERENCES item (id)
) ;

CREATE TABLE defaultPrice (
    itemId INT,
    price INT,
    PRIMARY KEY (itemId),
    FOREIGN KEY (itemId, price) REFERENCES price (itemId, price)
);

Answer 2

You could make a trigger that checks if there allready is a field with the 'TRUE' value, and if so take action.

Note that you cannot easily "reject" the update. (see e.g. : How to abort INSERT operation in MySql trigger? ).

You could for instance just insert it with false, and save your error somehow, by setting a flag.