patternsqlMinor

Foreign key part of composite primary key of new table

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

primarynewpartforeigncompositetablekey

Problem

I have tried searching loads but I have not received a concrete answer to my problem.
Suppose I have Table 'A' with primary key as Apk.

This Apk is a foreign key in Table 'B'. In Table 'B' I have also another column Bcol.

Now for 'B' can I have a composite primary key of 'Bcol' and 'Apk': PRIMARY KEY(Bcol, Apk)? Is it advisable?

Solution

so basically you have something like this (except I renamed your FK table to C since it was already pulling B from somewhere else):

CREATE TABLE IF NOT EXISTS `table_a` (
  `apk` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`apk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `table_b` (
  `bcol` varchar(3) NOT NULL,
  `Location` varchar(20) NOT NULL,
  `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `fk_table_c` (
  `apk` int(10) NOT NULL,
  `bcol` varchar(3) NOT NULL,
  `SomeOtherValue` varchar(20) NOT NULL,
  PRIMARY KEY (`apk`,`bcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `fk_table_c`
  ADD CONSTRAINT `fk_table_c_ibfk_2` FOREIGN KEY (`bcol`) REFERENCES `table_b` (`bcol`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_table_c_ibfk_1` FOREIGN KEY (`apk`) REFERENCES `table_a` (`apk`) ON DELETE CASCADE ON UPDATE CASCADE;

this is completely normal and how you would form many to many relationships between 2 entities. You may wish to have some other auto generated unique identifier as the primary key but its a good idea to have the 2 FKs as a combined unique key to prevent duplicating rows of the same combinations

Code Snippets

CREATE TABLE IF NOT EXISTS `table_a` (
  `apk` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`apk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `table_b` (
  `bcol` varchar(3) NOT NULL,
  `Location` varchar(20) NOT NULL,
  `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `fk_table_c` (
  `apk` int(10) NOT NULL,
  `bcol` varchar(3) NOT NULL,
  `SomeOtherValue` varchar(20) NOT NULL,
  PRIMARY KEY (`apk`,`bcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `fk_table_c`
  ADD CONSTRAINT `fk_table_c_ibfk_2` FOREIGN KEY (`bcol`) REFERENCES `table_b` (`bcol`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_table_c_ibfk_1` FOREIGN KEY (`apk`) REFERENCES `table_a` (`apk`) ON DELETE CASCADE ON UPDATE CASCADE;

Context

StackExchange Database Administrators Q#28660, answer score: 9

Revisions (0)

No revisions yet.