On delete set null creating table in mysql -

i create table named patientmedicinecategory . given below:-

 create table patientmedicinecategory  (  pmc         int(11) not null auto_increment,  patient_id  int(11) not null,  med_id      int(3) not null,  cat_id      int(3) not null,  testname    varchar(100) not null,   primary key(pmc),  unique key(patient_id, med_id,cat_id,testname),  foreign key(patient_id) references patient(patient_id) on update cascade on delete      cascade,  foreign key(med_id) references medicine(med_id) on update cascade on delete cascade,  foreign key(cat_id) references category(cat_id) on update cascade on delete set null  )engine=innodb; 

my patient table :-

  create table patient   (   patient_id    int not null auto_increment,   salutation    enum('mr.','mrs.','miss.','dr.') not null,   name      varchar(50) not null,   email_id  varchar(100),   year      int(4) not null,   month     int(3),   day       int(3),   sex       enum('m','f') not null,   contactno         varchar(50),        primary key(patient_id)   )engine=innodb; 

medicine table :-

  create table medicine   (   med_id  int(3) not null,   name    varchar(40) not null,    primary key (med_id)   )engine=innodb; 

category table :-

  create table category   (   cat_id    int(3) not null,   name      varchar(20) not null,    primary key (cat_id)   )engine=innodb; 

but when try create give error:-

   error 1005 (hy000): can't create table 'nutech3.patientmedicinecategory' (errno: 150) 

i have tried lot not success . please me . in advance

if log in root , run show engine innodb status you'll see exact error message this:

------------------------ latest foreign key error ------------------------ 130405 11:55:42 error in foreign key constraint of table test/patientmedicinecategory: foreign key(cat_id) references category(cat_id) on update cascade on delete set null  )engine=innodb: have defined set null condition though of columns defined not null. 

this offending line:

foreign key(cat_id) references category(cat_id) on update cascade on delete set null 

mysql cannot make patientmedicinecategory.cat_id null because defined way:

cat_id      int(3) not null, 

your options either:

• allow cat_id null
• set different on delete condition