Solved

Foreign key constraints on composite keys

Posted on 2006-06-14
6
504 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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