List:General Discussion« Previous MessageNext Message »
From:Christopher R. Jones Date:May 11 2000 2:16pm
Subject:Question on Primary Key
View as plain text  
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


Thread
Question on Primary KeyChristopher R. Jones11 May
Re: Question on Primary KeyChristopher R. Jones11 May
  • Re: Question on Primary KeySteve Ruby11 May