In answer to my own question:
if I name the PRIMARY KEY, then both fields are included in the key. The
documentation is not exactly clear on this point.
PRIMARY KEY PortPrj (PRJ_ID, PORT_ID));
Note the differences in the index definitions by simply naming the PRIMARY Key:
mysql> show index from user_projects;
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part |
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+
| user_projects | 1 | PRJ_ID | 1 | PRJ_ID |
A | NULL | NULL |
| user_projects | 1 | PORT_ID | 1 | PORT_ID |
A | NULL | NULL |
| user_projects | 1 | PORT_ID | 2 | TPL |
A | NULL | NULL |
| user_projects | 1 | ProjectSelect | 1 | PORT_ID |
A | NULL | NULL |
| user_projects | 1 | ProjectSelect | 2 | TPL |
A | NULL | NULL |
| user_projects | 1 | ProjectSelect | 3 | WTHFILE |
A | NULL | NULL |
| user_projects | 1 | ProjectSelect | 4 | HVACLABEL |
A | NULL | NULL |
| user_projects | 1 | ProjectSelect | 5 | VARIATION |
A | NULL | NULL |
| user_projects | 0 | PRIMARY | 1 | PRJ_ID |
A | 2 | NULL |
| user_projects | 0 | PRIMARY | 2 | PORT_ID |
A | 2 | NULL |
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+
10 rows in set (0.00 sec)
>mysqladmin Ver 7.8 Distrib 3.22.20, for Win95/Win98 on i586
>NT 4.0, service pak 6.
>
>Registered Windows user.
>
>I am trying to use a Primary Key made up of two fields:
>
>CREATE TABLE user_projects(
>PRJ_ID Char(10) NOT NULL,
>MODIFIED Date,
>WTHFILE Char(8) NOT NULL,
>BLDGTYPE Char(25) NOT NULL,
>TPL Char(4) NOT NULL,
>HVACLABEL Char(60) NOT NULL,
>VARIATION Char(40) NOT NULL,
>PORT_ID Char(23) NOT NULL,
>INDEX (PRJ_ID),
>INDEX (PORT_ID, TPL),
>INDEX ProjectSelect ( PORT_ID, TPL, WTHFILE, HVACLABEL, VARIATION),
>PRIMARY KEY (PRJ_ID, PORT_ID));
>
>My understanding is that the PRIMARY KEY index is a concatenation of the
>two fields. If this is so then I should be able to have two records:
>
>PRJ_ID PORT_ID
>L01gCAr 010100MA
>L01gCAr 010101MA
>
>But with the first record in place, the following statement fails:
>mysql> insert into user_projects set prj_id="L01gCAr", port_id="010101MA";
>
>ERROR 1062: Duplicate entry 'L01gCAr' for key 4
>mysql>
>
>Show index gives the following:
>mysql> show index from user_projects;
>+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+
>| Table | Non_unique | Key_name | Seq_in_index | Column_name
>| Collation | Cardinality | Sub_part |
>+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+
>| user_projects | 1 | PRJ_ID | 1 |
>PRJ_ID | A | NULL | NULL |
>| user_projects | 1 | PRJ_ID | 2 |
>PORT_ID | A | NULL | NULL |
>| user_projects | 1 | PORT_ID | 1 |
>PORT_ID | A | NULL | NULL |
>| user_projects | 1 | PORT_ID | 2 |
>TPL | A | NULL | NULL |
>| user_projects | 1 | ProjectSelect | 1 |
>PORT_ID | A | NULL | NULL |
>| user_projects | 1 | ProjectSelect | 2 |
>TPL | A | NULL | NULL |
>| user_projects | 1 | ProjectSelect | 3 |
>WTHFILE | A | NULL | NULL |
>| user_projects | 1 | ProjectSelect | 4 |
>HVACLABEL | A | NULL | NULL |
>| user_projects | 1 | ProjectSelect | 5 |
>VARIATION | A | NULL | NULL |
>| user_projects | 0 | PRIMARY | 1 |
>PRJ_ID | A | 8 | NULL |
>+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+
>10 rows in set (0.00 sec)
>
>Here the PRIMARY key shows only 1 column???
>
>Do I need to upgrade to a newer version of MySQL?
Christopher R. Jones, P.Eng.
14 Oneida Avenue
Toronto, Ontario M5J 2E3
Tel. 416 203-7465
Fax. 416 203-3044
Email cj@stripped
