RE: Newbie - Primary Keys

[Carlos Vazquez]

That's just what I wanted to do.
1 primary key made up with multiple fields.  In my case I will be a Date
Field for Transaction Date and a Integer field for StoreNumber.

Thanks a lot for your advice, guys.

:oD


-----Original Message-----
From: Mark V [mailto:news4mark@stripped] 
Sent: Monday, October 20, 2003 2:34 PM
To: mysql@stripped
Subject: Re: Newbie - Primary Keys

Good points Patrick. Thanks for the follow-up :-)

--- Patrick Shoaf <pshoaf@stripped> wrote:

---------------------------------
Mark is correct, you can define only 1 Primary Key,
but the primary keycan be made up of multiple fields. 
Example:

CREATE TABLE test (item_code char(5) NOT
NULL,item_color char(5) NOTNULL, data varchar(255),
PRIMARY KEY (ITEM_CODE,ITEM_COLOR));

This would allow multiple entries for an item_code and
multiple entriesfor item_color, but only 1 entry for a
combination. example

item_code | item_color | data
---------------------------
hammer    | red       | my favorite
hammer    | blue       |nice color 
nail      |red        | ugly item
nail      | green     | nice combination with red
hammer
name      |blue       | good with red hammer

With the option below you can only have 1 hammer, and
only 1 nail in theid1 field.  
You need to decide do you need multiple indexes, or do
you need 1 indexwith multiple data fields.

At 02:07 PM 10/20/2003, Mark V wrote:
Hi Carlos,

You can't have 2 primary keys. A table can only have
one primary key. You can, however, also define a
Unique key, and if you set it up such that it is not
null, it will act much like a prmary key:

CREATE TABLE test 
(  id1 INT UNSIGNED NOT NULL, 
   PRIMARY KEY (id1), 
   id2 INT UNSIGNED NOT NULL, 
   UNIQUE KEY (id2), 
   data VARCHAR(25)
);

Hope that helps,
Mark

--- Carlos Vazquez <Carlos@stripped> wrote:
> Hi all!
>  
> Just wanted to know how do I create a table with two
> primary keys.
>  
> Thanks a lot!
>  
> 


__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

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