mysql - Composite Foreign Key Constraint Not Enforced When NULL Allowed, Any Solution? -

i'm using mysql 5.6.20 , have following schema:

project activity table:

create table `prj_project_activity_detail` (     `fkprjprojectactivity` varchar(128) not null,     `fkprjprojectactivitysub` varchar(128) not null,     `fkprojectid` int(10) unsigned not null,     primary key (`fkprjprojectactivity`, `fkprjprojectactivitysub`, `fkprojectid`),     index `fk__prj_project_activity` (`fkprjprojectactivity`, `fkprojectid`),     index `fk__prj_project_activity_2` (`fkprjprojectactivitysub`, `fkprojectid`),     constraint `fk__prj_project_activity` foreign key (`fkprjprojectactivity`, `fkprojectid`) references `prj_project_activity` (`prjprojectactivity`, `fkprojectid`) on update cascade,     constraint `fk__prj_project_activity_2` foreign key (`fkprjprojectactivitysub`, `fkprojectid`) references `prj_project_activity` (`prjprojectactivity`, `fkprojectid`) on update cascade ) collate='utf8_general_ci' engine=innodb; 

data:

fkprjprojectactivity    fkprjprojectactivitysub     fkprojectid activity 1                  sub activity 1              1 activity 1                  sub activity 2              1 activity 1                  sub activity 3              1 

transaction table:

create table `str_transaction` (     `strtransactionid` varchar(32) not null,     `fkprjprojectactivity` varchar(128) null default null,     `fkprjprojectactivitysub` varchar(128) null default null,     `fkprojectid` int(10) unsigned null default null,     primary key (`strtransactionid`),     index `fk_str_transaction_department` (`fktransactionforstoredepartmentid`),     index `fk_str_transaction_prj_project_activity_detail` (`fkprjprojectactivity`, `fkprjprojectactivitysub`, `fkprojectid`),     constraint `fk_str_transaction_prj_project_activity_detail` foreign key (`fkprjprojectactivity`, `fkprjprojectactivitysub`, `fkprojectid`) references `prj_project_activity_detail` (`fkprjprojectactivity`, `fkprjprojectactivitysub`, `fkprojectid`) on update cascade, ) collate='utf8_general_ci' engine=innodb; 

expected enforce data:

strtransactionid    fkprjprojectactivity    fkprjprojectactivitysub     fkprojectid 1                       activity 1                  sub activity 2          1 2                       activity 1                  sub activity 3          1 

but allows me insert data shown below:

unexpected result:

strtransactionid    fkprjprojectactivity    fkprjprojectactivitysub     fkprojectid 1                       dummy 1                 sub activity 2              (null) 2                       activity 1              (null)                      1 3                       dummy 1                 dummy 1                     (null) 

the weird thing is, accepts data not in parent table.

is foreign key constraint enforces when columns contain data?

i want enforce constraint if of column contains data or columns must null.

mysql doc. says, (link)

the match clause in sql standard controls how null values in composite (multiple-column) foreign key handled when comparing primary key. mysql implements semantics defined match simple, permit foreign key or partially null. in case, (child table) row containing such foreign key permitted inserted, , not match row in referenced (parent) table. possible implement other semantics using triggers.

i cannot make "not null" basic requirement. have many other fields in str_transaction table (irrelevant question) removed here better clarity of question.

is practice implement trigger suggested in doc? or can me design architecture?

like eggyal suggested, create autoincrement primary key prj_project_activity_detail table , foreign key str_transaction table references it.

the structure that:

create table `prj_project_activity_detail` (     `prjprojectactivitydetailid` int(10) unsigned not null auto_increment,     `fkprjprojectactivity` varchar(128) not null,     `fkprjprojectactivitysub` varchar(128) not null,     `fkprojectid` int(10) unsigned not null,     primary key (`prjprojectactivitydetailid`),     index `fk__prj_project_activity` (`fkprjprojectactivity`, `fkprojectid`),     index `fk__prj_project_activity_2` (`fkprjprojectactivitysub`, `fkprojectid`),     constraint `fk__prj_project_activity` foreign key (`fkprjprojectactivity`, `fkprojectid`) references `prj_project_activity` (`prjprojectactivity`, `fkprojectid`) on update cascade,     constraint `fk__prj_project_activity_2` foreign key (`fkprjprojectactivitysub`, `fkprojectid`) references `prj_project_activity` (`prjprojectactivity`, `fkprojectid`) on update cascade ) collate='utf8_general_ci' engine=innodb;  create table `str_transaction` (     `strtransactionid` varchar(32) not null,     `fkprjprojectactivitydetailid` int(10) unsigned null default null,     primary key (`strtransactionid`),     index `fk_str_transaction_prj_project_activity_detail` (`fkprjprojectactivitydetailid`),     constraint `fk_str_transaction_prj_project_activity_detail` foreign key (`fkprjprojectactivitydetailid`) references `prj_project_activity_detail` (`prjprojectactivitydetailid`) on update cascade ) collate='utf8_general_ci' engine=innodb; 

now not able insert invalid data in example, because foregn key either existing prjprojectactivitydetailid or null.

to simulate old str_transaction table can create view left join:

create view `str_transaction_view`     select        st.strtransactionid,       pad.fkprjprojectactivity,       pad.fkprjprojectactivitysub,       pad.fkprojectid     str_transaction st     left join prj_project_activity_detail pad       on pad.prjprojectactivitydetailid = st.fkprjprojectactivitydetailid; 

sample