List:General Discussion« Previous MessageNext Message »
From:fbsd_user Date:March 8 2006 7:39pm
Subject:RE: Table with multiple primary keys - How
View as plain text  
Thank you.
I went with your solution and the create table worked.
Tested inserting on primary key and it worked.
Need to load more data before I can test alt indexes.

-----Original Message-----
From: mysql@stripped [mailto:mysql@stripped]
Sent: Wednesday, March 08, 2006 3:39 AM
To: mysql@stripped
Subject: Re: Table with multiple primary keys - How



It is not possible to have more than one PRIMARY
key per table.

Maybe you need to use one PRIMARY key as the main index into
the table, then use UNIQUE or KEY which is a synonym for
INDEX on the other two columns.

This book will help you ALOT with designing tables.

It will also teach you how to normalise (refactor) your
tables into a more efficient form.

http://www.apress.com/book/bookDisplay.html?bID=338

It is also more efficient IMHO to index on integer values if
you can, rather than character text.

Regards

Keith

> create table members (
>         logon_id            MEDIUMINT UNSIGNED NOT NULL
AUTO_INCREMENT,
>         email_addr          varchar(30), ???
> 	  last_name           varchar(30), ???
> 	  member_type         char(1),
>         email_verified      char(1),
> 	  logon_pw            varchar(15),
> 	  date_added          date,
>         last_login          timestamp,
>         first_name          varchar(30),
>         addr1               varchar(30),
>         addr2               varchar(30),
>         city                varchar(20),
>         state               varchar(20),
>         zip                 varchar(15),
> 	  phone_home          varchar(15),
>         phone_office        varchar(15),
>         phone_cell          varchar(15),
>         mothers_maiden_name varchar(30),
> 	  ip_of_useratsignup  varchar(16),
> 	  primary key(login_id, email_addr, last_name)
> 	);

primary key login_id (login_id),
key email_addr (email_addr),
key last_name (last_name)
);

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1


Thread
Table with multiple primary keys - Howfbsd_user8 Mar
  • Re: Table with multiple primary keys - HowDan Nelson8 Mar
  • Re: Table with multiple primary keys - HowRhino8 Mar
  • Re: Table with multiple primary keys - Howmysql8 Mar
    • RE: Table with multiple primary keys - Howfbsd_user8 Mar