0

How can I define a primary key in my table as two unique fields?

I have several columns in my table and I have two keys: ID and SOURCE. Values for ID can appear twice in the column, but the combination of ID + SOURCE can be relied on as a primary key.

For example:

              ID | SOURCE | TEXT
              ------------------
UNIQUE -      2312 | FB | BLA BLA  
UNIQUE -      2312 | MU | BLA BLA

I'm using MySQL database. How I can I make the primary key on two columns?

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Yehonatan
  • 3,168
  • 7
  • 31
  • 39
  • possible duplicate of [ALTER TABLE to add a composite primary key](http://stackoverflow.com/questions/8859353/alter-table-to-add-a-composite-primary-key) – piotrm May 27 '14 at 09:30

2 Answers2

3

You may use the ALTER TABLE syntax, like this:

ALTER TABLE my_table ADD CONSTRAINT my_table_pk PRIMARY KEY (id, source);

Also, this can be defined in CREATE TABLE, like this:

CREATE TABLE my_table
(
  id int,
  source varchar(3),
  text varchar(20),
  PRIMARY_KEY(id, source)
)
Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
  • Thank you for the detailed answer. I'm picking Aman's question because he have less points though your answer was more detailed. – Yehonatan May 27 '14 at 09:43
  • @Yehonatan I appreciate your effort around community building. Your comment reminds me of similar scenario a couple of years ago when my reputation points were low, a user gave same reason for accepting my answer over a high-rep user :) – Aziz Shaikh May 27 '14 at 09:49
  • @Thanks :) I'm trying to help as much as I can – Yehonatan May 27 '14 at 10:02
2

Add a constraint to enforce uniqueness enforced on two columns:

Alter table table_name add CONSTRAINT constr_ID unique (ID,SOURCE);

OR add a constraint primary key enforced on two columns:

 Alter table table_name add  PRIMARY KEY (id, source);
Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81