Defining secondary keys in MYSQL

[TheDust]

How on Earth are you supposed to define a secondary key in a mysql table. For instance, when creating a table, what would the syntax be? I can't define two columns as "PRIMARY KEY"- that's an error.

Can anyone help me out here?? Thanks in advance...

 

[sleipnir214]

Yes, only one column can be the primary key. But then that makes metaphoric sense -- how can more than one of something be the primary thing? Other columns can be indeces, too, though.

When you create the table, you can use the INDEX keyword to define an index:

create table foo (
id int unsigned not null auto_increment primary key,
foo varchar(10) not null,
bar int unsigned not null default(3),
index by_verb (verb));

[See:

http://www.mysql.com/doc/en/CREATE_TABLE.html

for a lot more detail]


You can add an index to an existing table:

alter table foo add index by_bar (bar)
or
create index by_bar on foo (bar)

[See:

http://www.mysql.com/doc/en/ALTER_TABLE.html

or

http://www.mysql.com/doc/en/CREATE_INDEX.html

for a lot more detail]

Indeces may be multi-column and use only part of a column. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[petersJazz]

How can more than one of something be the primary thing?

Its nothing strange about having two ore more columns adding upp to a primary key. Example:

Order with orderid as primary key

Orderrow with orderid and rowid as primary key

 

[TheDust]

Yeah, that's I was looking to do- have two columns that add up to a primary key...

In this case, it's the ALBUM id and the TRACK #... does that make sense? The albumID is a foreign key, and for each album there should only be one instance of each track, and that's where the trackNum column comes in...

 

[sleipnir214]

PetersJazz:
In that case you still have one thing being primary. That one thing just happens to contain two other things.

TheDust:
If you want to create a table with a multicolumn primary key:

CREATE TABLE foo (
col_1 int unsigned not null,
col_2 int unsigned not null,
col_2 varchar(25),
PRIMARY KEY (col_1, col_2));

Again, take a look at the links I posted above.


Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[petersJazz]

But you wrote: "there can only be one column in a primary key", and thats wrong. Even if you meant something else.

 

[sleipnir214]

petersJazz:

You're right. I should have said, "there can be only one primary key" Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!