Mysql composite primary key with set foreign key

Foreign key referencing only part of composite primary key

Primary Key = UNIQUE + NOT NULL + Automatic Indexed

Primary key can be defined for a combination of columns, When a primary key consist of multiple columns, then it is called a Composite Primary Key.

MySql Create Foreign Key

Create foreign key referencing to primary key

ALTER TABLE  `tb2` ADD FOREIGN KEY (  `tb1_ccid` ) REFERENCES  `test`.`tb1` (

`ccid`) ON DELETE RESTRICT ON UPDATE RESTRICT ;

Create foreign key referencing to composite primary key

ALTER TABLE  `tb2` ADD FOREIGN KEY (  `tb1_ccid` ,  `tb1_sid` ) REFERENCES  `test`.`tb1` ( `ccid` ,`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT

Example

create table tb1
( A integer not null
, B char(2) not null
, C integer not null
, primary key (A,B,C)
, unique (A,B)
);

create table tb2
( M integer not null
, N char(2) not null
, Z datetime
, foreign key (M,N) references tbl1 (A,B)
)