Why is composite primary key bad?

[Lightee]

I notice that CakePHP does not allow composite primary key. Why is this so? Is it because composite primary keys are bad for some reason or is it simply to stick to convention? I have been using MS Access and there is no such restriction.

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
 
 
To unsubscribe from this group, send email to
[hidden email] For more options, visit this group at http://groups.google.com/group/cake-php

[Jeremy Burns | Class Outfit]

A generalised statement: composite primary keys are bad. Each table should have a single field primary key that uniquely identifies 'this row'. If the table holds information that represents the intersection of two (or more) other tables, add them as additional fields with indexes on each with a foreign key constraint to its related table (you need to use InnoDB for this to be possible), then add a unique index that spans the fields that make up the composite key. Beside any good database driven reason why you should avoid composite primary keys, sticking to the Cake conventions makes your life so much easier, so given the opportunity to design the database with that in mind you ought to do so.

Jeremy Burns

Class Outfit

http://www.classoutfit.com

On 7 Aug 2012, at 01:40:43, Lightee <[hidden email]> wrote:

I notice that CakePHP does not allow composite primary key. Why is this so? Is it because composite primary keys are bad for some reason or is it simply to stick to convention? I have been using MS Access and there is no such restriction.

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
 
 
To unsubscribe from this group, send email to
[hidden email] For more options, visit this group at http://groups.google.com/group/cake-php

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
 
 
To unsubscribe from this group, send email to
[hidden email] For more options, visit this group at http://groups.google.com/group/cake-php

[Steve Found]

On 07/08/12 01:40, Lightee wrote:

> I notice that CakePHP does not allow composite primary key. Why is
> this so? Is it because composite primary keys are bad for some reason
> or is it simply to stick to convention? I have been using MS Access
> and there is no such restriction.
> --
> Our newest site for the community: CakePHP Video Tutorials
> http://tv.cakephp.org
> Check out the new CakePHP Questions site http://ask.cakephp.org and
> help others with their CakePHP related questions.
>
>
> To unsubscribe from this group, send email to
> [hidden email] For more options, visit this
> group at http://groups.google.com/group/cake-php

Composite primary keys are not 'bad' but they are ALWAYS replaceable. At
the end of the day, a primary key uniquely identifies a record in a
database table and CakePHP relies on this being either an auto
incrementing integer or a 36 character UUID.

The reason Cake does this is simple. Primary key values like this can be
generated automatically and Composite primary keys can't. A composite
key is tailored to your data so Cake cannot possibly work out what the
values should be, you have to do it by hand. If Cake cannot generate
primary key values then the Model code gets broken... what is Cake
supposed to do when you save a record and it needs to generate a new key ?

You could create an AppModel subclass to implement this yourself which
would not be too arduous as you know what the data is, but it is far
easier to make the composite primary key into keys then add another
auto-incrementing key as the primary. All you need to do then is add
code into your Cake generated models to handle your composite keys for
CRUD by implementing the Model callbacks.

I do not know of any PHP frameworks that handle Composite keys very
well, since they ALL require a way of determining which values to put in
them.

Steve.

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org 
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.


To unsubscribe from this group, send email to
[hidden email] For more options, visit this group at http://groups.google.com/group/cake-php

[Mark Wratten]

One possible exception might be HABTM tables, consisting of just the foreign keys to the related tables. MySQL only supports clustering on the primary key and using a composite primary key would give you a covered index, which in theory should perform a bit better. Though in practice, I have not found much difference and usually add an integer primary key column to those tables as well.

--
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to [hidden email].
To unsubscribe from this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php?hl=en-US.