5

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?

2
  • Please show CREATE TABLE for your tables.
    – Devart
    Commented Nov 14, 2012 at 7:18
  • Actually its a general question. I just popped up in my mind..:)
    – Bhumi Singhal
    Commented Nov 14, 2012 at 7:40

3 Answers 3

9

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

3

Yes. It depends on your circumstances and preferences. I like to have all my tables have single/noncomposite primary keys. You can see a working example in SQL Fiddle here: http://sqlfiddle.com/#!2/b05df/1

1

This is not possible. The foreign key can not refer to part of composite primary key of other table. Because it is supposed to be one-to-one relationship and if you refer just part of primary-key, there might be more than one parent record available, which is neither allowed nor possible. i.e. Database will not allow you to do something where situation may arise that one child refers to multiple parent in 1-to-1 relation.

1
  • I'll add that this isn't technically true - while partial key matches are not allowed, a table can have multiple UNIQUE KEY constraints (MySQL and SQL Server support using unique-keys as a kind of "secondary key") - which are handy for having a single-column foreign-key to a table with a composite primary key.
    – Dai
    Commented Jun 11, 2020 at 5:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.