NYCPHP Meetup

[David Mintz]

On Sat, 14 Jun 2003, Peter Lehrer wrote:

> Is it possible to have two primary keys in one table, such as primary key 1
> and primary key 2, or is that a contradiction in terms? Would second primary
> key have to be a "unique"?

You can only have one primary key. If you're thinking about a unique index
on multiple columns, yes you can do that, and indeed there are situations
where you do. You might not need any primary key in that case.

I'll give you an example. I have a table called 'interpreter' (of
languages) and a table called 'language'. Some interpreters have multiple
working languages; lots of languages are spoken by more than one
interpreter. You resolve the many-to-many relationship with a third table
called interpreter_language which looks something like this (English is
asumed in this particular application, so I only one the 'foreign'
language to store each interpreters' language pair). Note the unique key:

CREATE TABLE interpreter_language (
  interpreter_id smallint(5) unsigned NOT NULL default '0',
  language_id smallint(5) unsigned NOT NULL default '0',
  UNIQUE KEY idx1 (interp_id,lang_id)
) TYPE=MyISAM;

Insertion fails if somebody tries to enter a duplicate record.

HTH!


---
David Mintz
http://davidmintz.org/

     "You want me to pour the beer, Frank?"