Multiple Primary Key

[Thotheolh]

Hi, I tried to get a table to have multiple primary key using the
following command:

ALTER TABLE user ADD CONSTRAINT userPK PRIMARY KEY(uid,uname);

But it seems it throws:

Attempt to define a second primary key [90017-72] 90017/90017 (Help)

Hmmm... does H2 supports multiple primary key per table ?

[Thomas Mueller]

Hi,

No. Is there a database that does?

Regards,
Thomas

[Thotheolh]

If I am not wrong, I could do it for MySQL.

Here's some sample from MySQL statement:

CREATE TABLE /*!32312 IF NOT EXISTS*/ `user` (
`uid` int(25) NOT NULL auto_increment,
`uname` varchar(25) NOT NULL,
`upass` varchar(25) NOT NULL,
`uemail` varchar(100) NOT NULL,
`uaddr` varchar(50) NOT NULL,
`ucontact` varchar(30) NOT NULL,
PRIMARY KEY (`uid`,`uname`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

I remember they call it compund key or something... maybe I remembered
wrongly but MySQL could.

I got around the problem for now by making one as a primary key and
another a unique.

[Joshua Paine]

On Fri, May 16, 2008 at 9:45 PM, Thotheolh <twzg...@gmail.com> wrote:
> If I am not wrong, I could do it for MySQL.

> PRIMARY KEY (`uid`,`uname`)

This may not do what you think it does. A compound key is entirely
different from having two primary keys. What that line means is that
the combination of uid and uname must be unique--it actually breaks
your presumed intended uniqueness constraint, making it so that two
rows can have the same uid so long as they don't also have the same
uname (and vice-versa).

h2 supports compound keys the same as most other databases, and (as
best as I can tell) with the syntax you used. The error message you
received is not complaining about the compound key but about the fact
that you had already defined a primary key before--possibly by setting
a column to auto_increment? (Does that do it?)

--
Joshua Paine
Chief Tower Builder
LetterBlock Software
http://letterblock.com/

[Thotheolh]

I think you are right , Joshua. I have an auto increment. So how do I
create a comound key with auto increment ?

[Joshua Paine]

I'm not sure how to create a column that is auto_increment without
making it the primary key. However, you can fix things up after the
fact:

alter table mytable drop primary key;
alter table mytable add primary key (col1, col2);

[Thomas Mueller]

Hi,

Yes, I think this will solve the problem.

In most cases an auto-increment column is also the primary key. As far
as I know, this behavior is required for HSQLDB compatibility. But it
looks like in MySQL this is not the case. I will have a look if it is
possible to support both.

Regards,
Thomas