• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

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?
0
PurpleSlade
Asked:
PurpleSlade
  • 3
  • 3
1 Solution
 
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
 
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
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
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
 
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
 
PurpleSladeAuthor Commented:
thanks angel
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now