Composite Key
-
Gilbert Consellado wrote:
But why MySQL accept this query
..because it defines a single key, which happens to consist of two fields. It is still a single primary key.
Gilbert Consellado wrote:
what are you referring about "design-error"?
I mean that there can only be one "primary". Having multiple fields in a primary key is quite common. Yes, a larger key has a slight performance penalty; it has to check more data than in a single field, and any index containing the key will also grow.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
last question, which is better of the two? #1
CREATE TABLE IF NOT EXISTS `tbl_sample`
(
`tbl_sample_id` INT UNSIGNED NOT NULL,
`tracked_id` INT UNSIGNED NOT NULL,
-- other columns goes here,
PRIMARY KEY (`tbl_sample_id`, `tracked_id`)
)ENGINE = MyISAM#2
CREATE TABLE IF NOT EXISTS `tbl_sample`
(
`tbl_sample_id` INT UNSIGNED NOT NULL,
`tracked_id` INT UNSIGNED NOT NULL,
-- other columns goes here,
PRIMARY KEY (`tbl_sample_id`),
INDEX `track` (`track_id` ASC)
)ENGINE = MyISAM -
last question, which is better of the two? #1
CREATE TABLE IF NOT EXISTS `tbl_sample`
(
`tbl_sample_id` INT UNSIGNED NOT NULL,
`tracked_id` INT UNSIGNED NOT NULL,
-- other columns goes here,
PRIMARY KEY (`tbl_sample_id`, `tracked_id`)
)ENGINE = MyISAM#2
CREATE TABLE IF NOT EXISTS `tbl_sample`
(
`tbl_sample_id` INT UNSIGNED NOT NULL,
`tracked_id` INT UNSIGNED NOT NULL,
-- other columns goes here,
PRIMARY KEY (`tbl_sample_id`),
INDEX `track` (`track_id` ASC)
)ENGINE = MyISAMThe upper uses two fields to identify a single record. The lower one uses a single field; they would allow different things. I'd recommend to use the smallest set of fields required. Imagine you are the end-user, how do you differentiate between each record? What is it that makes a sample unique? If a sample can only be identified by giving both a tbl_sample_id and a track_id, they'd need both be part of the key. If either can be used, then the smallest would become the key, and the other a unique index (the upper in the first case, lower in the second case).
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
There cannot be two primary keys; such is always a design-error. You can have multiple fields that uniquely identify the field, but only one of them will be the actual primary key. The others are 'candidate keys', 'unique constraints'. No, you don't need a primary key to define a relation, but I would urge to not to. And yes, it will hurt performance.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
Agree with Eddy... and, if you're of an age, think of "Highlander"... there can be only one.
-
The upper uses two fields to identify a single record. The lower one uses a single field; they would allow different things. I'd recommend to use the smallest set of fields required. Imagine you are the end-user, how do you differentiate between each record? What is it that makes a sample unique? If a sample can only be identified by giving both a tbl_sample_id and a track_id, they'd need both be part of the key. If either can be used, then the smallest would become the key, and the other a unique index (the upper in the first case, lower in the second case).
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
Got it, Thank you
-
Just a dumb question about composite primary key. I am designing a DB, then i have a specific problem that having 2 primary keys can only solve. Now I am wondering if having 2 primary keys could hurt performance. Could it be? I am using MySQL 5.6, and in MyISAM engine. Any advice will be appreciated. Thank you
Agree with Eddy Vluggen. To add a few things:
- Personally I would define any column combinations that are unique in real world as unique, natural keys, not as primary keys.
- Beyond that I would define a single columns surrogate key for all tables and always fetch/modify rows based on this surrogate key.
For more information, see Surrogate key[^]
-
Agree with Eddy... and, if you're of an age, think of "Highlander"... there can be only one.
ohh, but I don't know what Highlander is :confused:
-
Got it, Thank you
-
Agree with Eddy Vluggen. To add a few things:
- Personally I would define any column combinations that are unique in real world as unique, natural keys, not as primary keys.
- Beyond that I would define a single columns surrogate key for all tables and always fetch/modify rows based on this surrogate key.
For more information, see Surrogate key[^]
Yup, and Edd save me on this. But, I am not able or somehow use surrogate keys on this design.
-
ohh, but I don't know what Highlander is :confused:
Highlander - a 1986 sci-fi movie. If you watch it, the comment will become clear.
-
Just a dumb question about composite primary key. I am designing a DB, then i have a specific problem that having 2 primary keys can only solve. Now I am wondering if having 2 primary keys could hurt performance. Could it be? I am using MySQL 5.6, and in MyISAM engine. Any advice will be appreciated. Thank you
I go slightly against the grain here, I use a LinkID field for many to many tables, what you are describing, setting that as the primary key. The 2 foriegn key fields can then have a unique constraint applied. And yes this adds a performance cost, minor unless you are using serious volumes, but if there are tables referencing the link table you only need the linkid field rather than the 2 fields making up the composite key.
Never underestimate the power of human stupidity RAH
-
Yup, and Edd save me on this. But, I am not able or somehow use surrogate keys on this design.
Just to be clear, as far as I can see, you haven't talked about surrogate keys which is different from composite keys. The idea of surrogate is to have a key which has no relation to the real data, it only serves as an identifier, nothing more. What comes to usability in your design, I've never seen a situation where surrogate wouldn't be usable, but I'm not familiar with your case.