How to mark the default row to select in a many to many relationship To start off, I have the following tables:


products - a list of products
images - a list of images (which may or may not belong to a product)
product_images - a join table to connect products to images (this table just has id, product_id, and image_id)


I need a way to mark one image as the default image to use for a product, but I'm not quite sure how to set it up. I've thought of a few options but none of them feel correct.

Options I've thought of:


Add an image_id field to the products table for the default image. This seems bad cause the image on the product might not be an image that belongs to that product (I could take care of this on the application side, but I'm looking for more elegant solutions)
Add a column on the product_images table that marks that image as default, and add a unique constraint on the combined product_id field and default field (the product_images table would then look like: id, product_id, image_id, default). I've never used unique constraints before, and I'm not sure if they're the right way to go. 


Hope you guys have some better advice!
A UNIQUE constraint on (product_id, default) won't work since you can have several images that are not default for each product. A similar approach is to add a display_order attribute:

CREATE TABLE product_images
( product_id ... NOT NULL
      references ...
, image_id ... NOT NULL
      references ...
, display_order SMALLINT NOT NULL
,     PRIMARY KEY (product_id, display_order) )


To get the default image for a product choose the one with the smallest display_order for that product.

Depending on your business requirements you may add an alternative key. Examples:

-- An image may not be reused between products
ALTER TABLE product_images ADD CONSTRAINT ...
    UNIQUE (image_id)

-- An image may not be used several times for each product
ALTER TABLE product_images ADD CONSTRAINT ...
    UNIQUE (image_id, product_id)


To get the default image for a product:

SELECT image_id
FROM product_images
WHERE product_id = ?
ORDER BY display_order
LIMIT 1;


EDIT: I keep forgetting this bug in MySQL ...

To assign a new default image for a product:

UPDATE product_images x     
    SET display_order = (SELECT min from (SELECT MIN(display_order)-1 as min                                        FROM product_images                          
                                          WHERE product_id = ?) as y)
WHERE product_id = ?   
  AND image_id = ?;


or possibly:

UPDATE product_images x     
    SET display_order = (SELECT min 
                         FROM (
                             SELECT display_order-1 as min                         
                             FROM product_images                          
                             WHERE product_id = ? 
                             ORDER BY display_order LIMIT 1
                         ) as y
                        ) 
WHERE product_id = ?
  AND image_id = ?;