Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Please show CREATE TABLE for your tables. –  Devart Nov 14 '12 at 7:18
    
Actually its a general question. I just popped up in my mind..:) –  Bhumi Singhal Nov 14 '12 at 7:40
add comment

migrated from stackoverflow.com Nov 14 '12 at 12:57

This question came from our site for professional and enthusiast programmers.

2 Answers

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.