show columns from score, where student_id is a foreign key to one
table, and event_id a foreign key to another.

+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default |
Extra |
+------------+------------------+------+-----+---------+-------+
| student_id | int(10) unsigned | NO | PRI | |
|
| event_id | int(10) unsigned | NO | PRI | |
|
| score | int(11) | NO | | |
|
+------------+------------------+------+-----+---------+-------+


this blog gives the recipee, but it does not work for me
http://www.jroller.com/dschneller/en...primary_key_to

mysql> ALTER TABLE child
-> ADD COLUMN child_id BIGINT(20) AUTO_INCREMENT NOT
NULL FIRST,
-> ADD PRIMARY KEY(child_id);

alter table score
-> add column id bigint(2) auto_increment not null
first,
-> add primary key(id);
ERROR 1068 (42000): Multiple primary key defined

what do I need to do to add a primary key to this table?

anne001 wrote:
> show columns from score, where student_id is a foreign key to one
> table, and event_id a foreign key to another.
>
> +------------+------------------+------+-----+---------+-------+
>> Field | Type | Null | Key | Default |
> Extra |
> +------------+------------------+------+-----+---------+-------+
>> student_id | int(10) unsigned | NO | PRI | |
>>
>> event_id | int(10) unsigned | NO | PRI | |
>>
>> score | int(11) | NO | | |
>>
> +------------+------------------+------+-----+---------+-------+
>
>
> this blog gives the recipee, but it does not work for me
> http://www.jroller.com/dschneller/en...primary_key_to
>
> mysql> ALTER TABLE child
> -> ADD COLUMN child_id BIGINT(20) AUTO_INCREMENT NOT
> NULL FIRST,
> -> ADD PRIMARY KEY(child_id);
>
> alter table score
> -> add column id bigint(2) auto_increment not null
> first,
> -> add primary key(id);
> ERROR 1068 (42000): Multiple primary key defined
>
> what do I need to do to add a primary key to this table?

The table already has a primary key. A table can have only one primary key.

How do I remove the primary key label on the foreign keys, so I can
add a single autoincrement primary key column?

alter table score drop primary key;
ERROR 1025 (HY000): Error on rename of './sampdb/#sql-185_9' to './
sampdb/score' (errno: 150)

I think it is because the table has two primary keys for some reason.

anne001 wrote:
> How do I remove the primary key label on the foreign keys, so I can
> add a single autoincrement primary key column?
>
> alter table score drop primary key;
> ERROR 1025 (HY000): Error on rename of './sampdb/#sql-185_9' to './
> sampdb/score' (errno: 150)
>
> I think it is because the table has two primary keys for some reason.
>
>
>

Why do you need an autoincrement value? student_id/event_id already is
unique; an autoincrement would be redundant.

The structure given is correct for a table such as this; only in very
rare circumstances should you need an autoincrememt value.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

> Why do you need an autoincrement value? student_id/event_id already is
> unique; an autoincrement would be redundant.

because for a join table, ruby on rails expects a single
autoincremental key, it cannot work with multiple keys.

I have copied the old table into a new table without the key type, but
it would be nice to know how to change the original table directly

anne001 wrote:
>> Why do you need an autoincrement value? student_id/event_id already is
>> unique; an autoincrement would be redundant.
>
> because for a join table, ruby on rails expects a single
> autoincremental key, it cannot work with multiple keys.
>
> I have copied the old table into a new table without the key type, but
> it would be nice to know how to change the original table directly
>
>

Read up on the ALTER TABLE command.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================