----- Original Message -----
From: "fbsd_user" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, March 07, 2006 11:40 PM
Subject: Table with multiple primary keys - How
What I am trying to do here is have 3 separate primary keys.
Creating a mysql select on either of the Logon_id, email_addr,
or last_name fields will do a single read to the matching value.
Like having 3 different indexes into the same table.
I don't want those 3 field concatenated together as a single key.
Is this table definition correct?
By definition, a table cannot have more than one primary key. Period. Your
definition will probably execute successfully - I haven't tried it so I
can't be sure - but 'successful' only means that it will (probably) not fail
on a syntax error. Your definition does NOT create three primary keys, it
creates one key on the combination of three values. In other words, your
definition concatenates the three keys together, which you say you don't
want to do.
Your basic goal of having three separate primary keys is not possible.
However, you _could_ create a primary key and two unique keys or three
unique keys. But before you start doing that, I suggest you take a step back
and rethink your design.
The proper way to design databases is to do logical design FIRST, then
consider physical design. In other words, make sure that your design hangs
together logically first and meets all your business requirements. Then, and
only then, should you consider physical design, i.e. redesigning tables to
optimize performance. Logical design is always done with the assumption that
you are working on a perfect processor that has outstanding performance no
matter what query you run. Once the logical design is perfect, you can start
being realistic and modify your design to optimize performance for real
world non-perfect processors.
I think you're leaping into physical design before you've finished logical
design. You're worried about query performance before you've satisfied
yourself that the logical design even works. You need to consider what
primary key will support your logical design. Is the login_id alone
sufficient to uniquely identify rows of the members table? Or do you need
other columns to accomplish this? If, in fact, you need all three columns
(login_id, email_addr, and last_name) to uniquely identify rows in the
members table, then all three of those need to be in your single,
concatenated key, exactly as you have it in your code.
Is it possible for more than one person to have the login_id ABC123? If not,
the login_id by itself should suffice to be your primary key. In that case,
you may want to make email_addr and last_name separate unique keys - or not.
If the login_id is sufficient to uniquely identify a member that's fine;
make login_id your primary key. But you may not want to make email_addr or
last_name unique keys. What if two of your members have separate login_ids
but share an email address? Making email_addr unique will prevent one of
your two members from being added to the table. This is an even bigger
problem for the last_name; if you make it unique, and you already have one
Smith in the table, you will never be allowed to have another Smith in the
table!
You need to sort out the issue of the primary key FIRST. Once that is
resolved, you can start to think about making other keys unique or not. But
right now, I think you're getting badly ahead of yourself.
create table members (
logon_id varchar(15),
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)
);
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 06/03/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
