How to use keys in a table so two columns are unique I want to create a basic database that handles photos. Also, i want to show  a photo of the day to the user.
I´ve created a table called ´photo´ and another table ´photooftheday´, like so

CREATE TABLE `photo` (
`photo_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`caption` VARCHAR(255) NOT NULL,
`filename` CHAR(29) NOT NULL,
PRIMARY KEY (`photo_id`)
)

CREATE TABLE `photooftheday` (
`photo_id` SMALLINT(5) UNSIGNED NOT NULL,
`date` DATE NOT NULL
)


I didn't add an auto increment column for the table photooftheday, coz I believe it’s not necessary (maybe I’m wrong).

My question is about the table ´photooftheday´. I don’t know how to handle keys so each day contains a photo that is not already selected in another day. In this case, i’m confused about primary keys, unique keys, keys, foreign keys, column indexes...

Also, if it helps, my query to select the photo of the day is going to use the date column, like:

$todays_date = date('Y-m-d');

$q = "SELECT p.photo_id, p.title, p.caption, p.filename
FROM photooftheday pd
JOIN photo p ON pd.photo_id = p.photo_id
WHERE DATE(pd.date) = '$todays_date'";


How do i handle the columns in the table photooftheday, so to optimize it?

Thanks
If I understand correctly, a given photo will have a maximum of one record in photooftheday, so you could use photo_id as the primary key of that table.  That would ensure uniqueness.

EDIT: Added based on first comment

Basically you will need one more index:

ALTER TABLE `photooftheday`
ADD UNIQUE INDEX `idx_date` (`date`);


That will ensure no two values in the column date will be the same.  As far as optimizing for the query, with the addition of the unique index you will have it covered:  You want indexes on the columns used in joins  (photo_id from each table which, as PKs, are already indexed) and in the WHERE clause (which has only date, which we just indexed).