Two column as a primary keys in mysql?

Question

Today I found out you can have a primary key using two columns (tsql). The PK must be unique but both columns do not (the combo must be unique).

I thought that was very cool. There were at least two SO question I asked where people yelled at me that I was doing my (mysql) databases wrong with only one person saying I did it fine. So... this leaves me some doubt

Does this do what I think it does?

create table User(
   id INT primary key AUTO_INCREMENT ,  
   ipaddr  TEXT  NOT NULL , 
   email  TEXT  NOT NULL
);
create table test(
  a  INT  NOT NULL ,
  b  INT  NOT NULL ,
  dummy  INT  NOT NULL ,
  FOREIGN KEY (a) REFERENCES User(id),
  FOREIGN KEY (b) REFERENCES User(id),
  PRIMARY KEY(a,b)
);

I ran the below so it appears that i is doing what i think (combos must be unique. But same value in a column don't need to be unique). Should i be aware of something? There must be a reason no one mentioned this to me in regards to mysql?

mysql> insert into test(a,b,dummy) select 1,1,1;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test(a,b,dummy) select 1,2,2;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test(a,b,dummy) select 2,1,3;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test(a,b,dummy) select 2,2,4;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test(a,b,dummy) select 1,2,5;
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'

Answer 1

You are over thinking this:

• primary keys (no matter how many columns are involved) must be unique
• your primary key is the two columns a, b

Therefore a and b together must be unique.

Individual value of a and b are irrelevant.

Answer 2

Yes, this is a normal thing to do in SQL and it works (having a composite primary key, in which multiple fields together constitute a unique value).

Two notes:

1. Make sure it's necessary. It often is and then it's fine. But sometimes it's a sign that you further need to normalize your data model.

2. I'm thinking that you don't want to make and a b be foreign keys from another table and then make them the composite primary key of your table. What happens if you set up a cascading delete, in which one user id, but not the other is deleted? So the composite primary key is fine, but then you don't want come from "unrelated" foreign keys.

Answer 3

Your thinking is good. I use multi-field primary keys frequently, simply because it makes my database design more logical, managable and readable. You can think of multi-field primary keys like having a unique name. For example:

Multi-Field Primary Keys:

(First ,Middle, Last)

Example Values:

('Michael', 'A.', 'Kline')

There can be many people with the 'First' name 'Michael' and/or the 'Middle' name 'A.' and/or the'Last' name 'Kline', but as far as your database is concerned, there can only be ONE 'Michael A. Kline'.

Usually, a multi-field primary key is a combination of other primary keys from other tables and the record contents describe content relavant to the specific key values. For example:

Table #1: Student Records (KEY: student_id)
Table #2: Course Records (KEY: course_id)
Table #3: Student Grades (KEY: student_id, course_id)

Hope this helps.

Answer 4

Yes you have to be aware of duplicating your PRIMARY_KEY which is a combined key in your case not to be duplicated.

Anyway when you set two PK's this means that your signature is PK1+PK2 so you can duplicate PK1 or PK2 but not the both of them.

Hope that I helped

Answer 5

Also one thing to note: Primary keys are automatically indexed in MySQL. And order of columns you mention in primary key does matter for performance as mentioned here

Answer 6

I believe what is happening is the paired columns together are a primary. For example you know you cannot have a duplicate primary column Ex: if col "a" is primary you cannot have two rows that have the same value for a.

In this example you have two primaries; which means you can only have one unique value for each col pair. For example if col 'a' and 'b' are primary and 'c' is not: a|b|c 1,2,3 works 1,4,5 works and 5,1,6 works 9,1,10 works

but you cannot have: 9,8,10 9,8,6 because for that (9,8) pair you can only have one unique value...

Does that make sense or would you like me to further elaborate?

Answer 7

ALTER TABLE TableName DROP PRIMARY KEY, ADD PRIMARY KEY( column1, column2); if you have set primary key previously, then try this.

Answer 8

To make it easier to explain, I will only use one table. Create a table with 2 int columns, and a PK on both of them together. As in the question.

create table test(
  a  INT  NOT NULL ,
  b  INT  NOT NULL ,
  PRIMARY KEY(a,b));

Now we can add rows, until we get an error

insert into test values(1,1); 
Query OK, 1 row affected (0,00 sec)

insert into test values(1,2); 
Query OK, 1 row affected (0,00 sec)

insert into test values(1,1); 
ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'

Which is logical because the combined values of the 2 columns which make up the PK are not unique anymore when this last statement would be executed.

It is allowed to store 2x the value 1 in a, because that is not the PK. The PK is the combined value of columns a and b.