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
Solved

Foreign key constraints on composite keys

Posted on 2006-06-14
6
503 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
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

837 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