Foreign key constraints on composite keys

CREATE TABLE `kappas_address` (
 `kappa_id` smallint(6) NOT NULL,      
 `address_id` int(11) NOT NULL,
  `addresstype_id` char(2) NOT NULL,
  PRIMARY KEY  (`kappa_id`,`address_id`),
 KEY `addresstype_id` (`addresstype_id`),
 CONSTRAINT `ka_kappas_id_fk` FOREIGN KEY (`kappa_id`) REFERENCES `kappas` (`kappa_id`),
 CONSTRAINT `ka_address_id` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`),
 CONSTRAINT `ka_addresstype_id` FOREIGN KEY (`addresstype_id`) REFERENCES `address_type` (`addresstype_id`)
) TYPE=InnoDB

I am trying to create foreign key constraints for both kappa_id and address_id using the above - however, it is only allowing me to create a foreign key constraint on kappa_id, not address_id.  How can I get it to do both?
LVL 2
PurpleSladeAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>And in fact in the above example to add a foreign key constraint to kappa_id you don't need to add  
>KEY `kappa_id` (`kappa_id`) so why do you have to do it for the second part of the key?  
as kappa_id is the first field in the implicit index for the primary key, it is truly index.
address_id is not truly index on it's own, but only a suffix to the kappa_id.
hence the need of the additional index starting with address_id
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
What about this:

CREATE TABLE `kappas_address` (
 `kappa_id` smallint(6) NOT NULL,      
 `address_id` int(11) NOT NULL,
  `addresstype_id` char(2) NOT NULL,
  PRIMARY KEY  (`kappa_id`,`address_id`),
 KEY `address_id` (`address_id`),
 KEY `addresstype_id` (`addresstype_id`),
 CONSTRAINT `ka_kappas_id_fk` FOREIGN KEY (`kappa_id`) REFERENCES `kappas` (`kappa_id`),
 CONSTRAINT `ka_address_id` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`),
 CONSTRAINT `ka_addresstype_id` FOREIGN KEY (`addresstype_id`) REFERENCES `address_type` (`addresstype_id`)
) TYPE=InnoDB
0
 
PurpleSladeAuthor Commented:
I guess what I'm failing to see is that even if that works, if the primary key is a composite key, why do I need to index one and not the other?  Shouldn't they both be treated as a Primary Key and thus not need to be indexed?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
indexes with multiple fields are basically indexes on the first field indexes, plus additional information (second, third field etc). the other fields in the index are not indexed as such, so for example:

table (a,b,c)
index1(a,b)

query: select * from table where b=x
-> will probably perform a full table scan.
the best what could happen is that it does a full index scan on the index1, and for the rows found, get them from the table. but it's not a index lookup.

In regards to the foreign key constraints, MySQL enforces you to have a index on the field (ie starting with that field).
if you wanted to make b a foreign key field to another table, you have to add a index like index2(b) or index2(b,a) or the like
0
 
PurpleSladeAuthor Commented:
OK, but what I'm still not understanding is this -

PRIMARY KEY  (`kappa_id`,`address_id`),

so right there it is identified as a composite key.  Now primary keys are automatically indexed, correct, which is why you don't ahve to create an index for them explicitly to add a foreign constraint?

And in fact in the above example to add a foreign key constraint to kappa_id you don't need to add  
KEY `kappa_id` (`kappa_id`) so why do you have to do it for the second part of the key?  

Sorry if I'm being a pain, I am just trying to understand :)

Are you saying that in your example
table(a,b,c) - even if you define the a,b fields as a composite key that it only indexes the first part of that?
0
 
PurpleSladeAuthor Commented:
thanks angel
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.