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


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