I have 3 tables as:
/*contains user id and name*/
create table kprofile
( kid bigint unsigned,
name varchar(30) not null,
constraint pkkprofile primary key(kid)
);
/*contains code and name of services*/
create table scode
(
code smallint unsigned,
name varchar(50) not null,
constraint pkscode primary key (code)
);
/*contains id of the user and code of services provided by him*/
create table kserviceprovide
(
kid bigint unsigned,
scode smallint unsigned not null,
constraint kserviceprovide primary key(kid,scode),
constraint fkkserviceprovideonkid foreign key(kid) references kprofile(kid),
constraint fkkserviceprovideonscode foreign key(scode) references scode(code)
);
In the table kserviceprovide,I am aware of concept that foreign key will not work at this time since it is also used as composite primary key.
But, somehow I need to implement such behaviour. I need to check for the presence of kid and scode in upper 2 tables before inserting any row in 3rd table.
How can I implement this behaviour?