Advanced unique constrant with MySQL -

boiled down essence of problem have mysql table (innodb) datetime field , need implement duplicate check not allow same date, down hour, used more once (eg. 1 row 2013-07-18 13:xx:xx).

my first question is, if there way enforce in mysql database itself?

otherwise approach along lines of:

1. lock table both read , writes (to avoid the
2. make select query verify can insert new row
3. insert it
4. unlock table again

i dislike solution - suggestions on how without having lock table appreciated.

there's no simple, declarative way in mysql. can create shadowing column, , use triggers keep data consistent. assumes "ts" (below) can valid timestamp, want 1 of them per hour.

create table test (    ts datetime not null,    ts_uniq char(13) not null,    unique (ts_uniq) ); 

the column "ts_uniq" shadowing column. hold strings '2013-01-01 08'.

create trigger bef_ins_test  before insert on test each row set new.ts_uniq = date_format(new.ts, '%y-%m-%d %h'); 

you'll need similar trigger executes before updates.

create trigger bef_upd_test  before update on test each row set new.ts_uniq = date_format(new.ts, '%y-%m-%d %h'); 

when insert values "ts", shadowing column automatically set correctly.

insert test (ts) values ('2013-01-01 08:35:33'); select * test;  ts                   ts_uniq -- 2013-01-01 08:35:33  2013-01-01 08 

trying insert different value fails correctly, raising error code 1062 (duplicate entry).

insert test (ts) values ('2013-01-01 08:47:13'); 

if update existing timestamp, before update trigger keeps column "ts_uniq" consistent.

update test set ts = '2013-01-01 17:42:42';  select * test;  ts                   ts_uniq -- 2013-01-01 17:42:42  2013-01-01 17 

trying independently update "ts_uniq" won't raise error, won't change row, either.

update test set ts_uniq = '2013-12-31 18';  select * test;  ts                   ts_uniq -- 2013-01-01 17:42:42  2013-01-01 17