0

Today someone asked me about composite primary key two make two column as primary key but i unable to understand the logic behind it because we have unique index option to make a column unique, and if I think about foreign key relationship then again its not required.

If anyone have good explanation with example then please clarify that.

CREATE TABLE `sample` (
  `pri_1` int(11) NOT NULL AUTO_INCREMENT,
  `pri_2` int(11) NOT NULL,
  `name` varchar(25) NOT NULL,
  `value` varchar(25) NOT NULL,
  PRIMARY KEY (`pri_1`,`pri_2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
  • 1
    or two primary keys in a table The table may have not more than one primary key. PK may include two fields. But this is ONE primary key. – Akina Dec 18 '19 at 9:02
  • AUTO_INCREMENT is not a requirement. Will name be unique in the table? Will value? Or is the combination of name and value unique? – Rick James Dec 19 '19 at 17:33
0

Assume we need a table to store the info about our pencils counts. They have separate colors and separate hardness.

We create a table

CREATE TABLE pencils (
    color VARCHAR(8),    -- red, green, blue, ...
    hardness VARCHAR(8), -- soft, hard, ...
    count INT,           -- the count of pencils with specific color and hardness
    PRIMARY KEY (color, hardness)
);

Firstly we insert that we have 1 soft red pencil, 2 soft green and 3 hard red.

INSERT INTO pencils VALUES ('red','soft',1), ('green','soft',2), ('red','hard',3);

The data will be inserted successfully, because each field pair is unique:

color_id,hardness_id | count
  red        soft    |  1
 green       soft    |  2
  red        hard    |  3

Pay attention: none separate value is unique, nevertheless everything was inserted.

We buy 4 hard green pencils and insert this into our table.

INSERT INTO pencils VALUES ('green','hard',4);

The data will be inserted successfully, because this field pair is unique too, there was no a record with this values pair previously. The table is

color_id,hardness_id | count
  red        soft    |  1
 green       soft    |  2
  red        hard    |  3
 green       hard    |  4

and each pair is unique again.

Now we buy 1 soft green pencil and try to insert a record:

INSERT INTO pencils VALUES ('green','soft',1);

This query will fail, because the values pair (color,hardness)=('green','soft') is already present in a table, and primary key duplicate will be detected.

To register the fact we must not insert new record but update existing record:

UPDATE pencils SET count=count+1 WHERE (color_id,hardness_id)=('green','soft');

This query will be executed succcessfully, and updated value 2+1=3 will be stored in the table now for soft green pencils. The table is now

color_id,hardness_id | count
  red        soft    |  1
 green       soft    |  3
  red        hard    |  3
 green       hard    |  4

and each pair is unique again.

| improve this answer | |
  • are you taking hardness_id as foreign key? – Sr. PHP Programmer Team Lead Dec 18 '19 at 9:19
  • thanks, its clear now, composite primary keys always works with combination check. – Sr. PHP Programmer Team Lead Dec 18 '19 at 9:58
  • one last question, can it works same with primary key auto-increment? – Sr. PHP Programmer Team Lead Dec 18 '19 at 10:01
  • @SeniorTeamLeadPHP It depends on a table engine. For InnoDB AI must be 1st field in composite PK expression (all another fields are excess for uniqueness but may be useful for covering). For MyISAM AI may be also 2nd, in that case it is autoincremented separately for each unique value in 1st field. – Akina Dec 18 '19 at 10:08
0

It comes in handy especially when combining with foreign keys ...

imagine having 3 tables

T1
ID, data1, data2
T2
ID, data1, data2
T3
T1id, T2id, data3, data4

the ID column for T1 and T2 is obviously primary key - but what about table T3 - we could define another ID column to make a primary key .. OR just define the combo of T1id and T2id as the primary key ... but this depends on what data you hold in your tables and what relationship they contain but especially when T3 was used to store a M:N (many : many) relation it makes sense to use a composite primary key consisting of T1id and T2id.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.