CODEWITHSUNDEEP
mysqlconstraints
pwaring
pwaring

Reputation: 3074

MySQL constraints involving multiple columns

I have a table in an application for which the current schema is:

CREATE TABLE quotes
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  quote_request_id INT UNSIGNED NOT NULL,
  quote_amount DECIMAL(12, 2) NOT NULL,
  accepted TINYINT UNSIGNED NOT NULL DEFAULT 0,
  FOREIGN KEY (quote_request_id) REFERENCES quote_requests(id)
) Engine=InnoDB;

I want to enforce a constraint such that only one quote can be accepted for a given quote request - i.e. an UPDATE or INSERT query should fail if it attempts to modify the table such that two or more rows with the same quote_request_id value will have an accepted value of 1.

Is this possible in MySQL? Enforcing constraints such as foreign keys, uniqueness of columns other than the primary key etc. work fine, and I can find information about applying a UNIQUE constraint to multiple columns, but I can't find anything about more complex constraints which involve multiple columns.

Upvotes: 2

Views: 345

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

If you want to do this without triggers, you can add another table where only accepted quotes will be stored - and you can remove the accepted column from the quotes table:

CREATE TABLE quotes
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  quote_request_id INT UNSIGNED NOT NULL,
  quote_amount DECIMAL(12, 2) NOT NULL,
  --- accepted TINYINT UNSIGNED NOT NULL DEFAULT 0,             --- removed
  FOREIGN KEY (quote_request_id) REFERENCES quote_requests(id)
  UNIQUE KEY (quote_request_id, id)               --- needed for the FK below
) Engine=InnoDB;

CREATE TABLE quotes_accepted
(
  id INT UNSIGNED NOT NULL PRIMARY KEY,
  quote_request_id INT UNSIGNED NOT NULL,
  UNIQUE KEY (quote_request_id),            --- this ensures there is only one
                                            --- accepted quote per request
  FOREIGN KEY (quote_request_id, id) 
    REFERENCES quotes(quote_request_id, id)
) Engine=InnoDB;

Upvotes: 1

pwaring
pwaring

Reputation: 3074

Answered by a_horse_with_no_name, but in a comment so it can't be accepted:

"I don't think this is possible without reverting to a trigger in MySQL because MySQL does not support partial indexes."

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80657

You mean you want a UNIQUE like this:

UNIQUE `quote_accepts` (`quote_request_id`, `accepted`)

where, for a repeat pair of quote_request_id & accepted, the INSERT will fail.

Upvotes: 0

Related Questions