Solved

Foreign key constraints on composite keys

Posted on 2006-06-14
6
505 Views
Last Modified: 2012-06-27
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
Comment
Question by:PurpleSlade
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16906275
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
 
LVL 2

Author Comment

by:PurpleSlade
ID: 16906359
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16906496
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 2

Author Comment

by:PurpleSlade
ID: 16906654
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 16907014
>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
 
LVL 2

Author Comment

by:PurpleSlade
ID: 16917615
thanks angel
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question