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

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

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

    
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

3 Answers 3

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

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

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.

share|improve this answer

Your Answer

 
discard

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