#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    17
    Rep Power
    0

    Why is primary constraint still named primary and index not hash?


    Hi

    this is my version of mysql

    Code:
    mysql> show variables where variable_name like '%version%';
    +-------------------------+------------------------------+
    | Variable_name           | Value                        |
    +-------------------------+------------------------------+
    | innodb_version          | 1.1.8                        |
    | protocol_version        | 10                           |
    | slave_type_conversions  |                              |
    | version                 | 5.5.27                       |
    | version_comment         | MySQL Community Server (GPL) |
    | version_compile_machine | x86                          |
    | version_compile_os      | Win32                        |
    +-------------------------+------------------------------+
    7 rows in set (0.00 sec)
    here's my create table statement:

    Code:
    create table if not exists test1(
    test1_id int,
    test1_string varchar(10),
    constraint pk_test1 primary key (test1_id) using hash comment 'primary key index for test1'
    );
    when executing show create table

    Code:
    mysql> show create table test1;
    +-------+-----------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------+
    | Table | Create Table
    
                                                                              |
    +-------+-----------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------+
    | test1 | CREATE TABLE `test1` (
      `test1_id` int(11) NOT NULL DEFAULT '0',
      `test1_string` varchar(10) DEFAULT NULL,
      PRIMARY KEY (`test1_id`) USING HASH COMMENT 'primary key index for test1'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+-----------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------+
    1 row in set (0.01 sec)
    if you observed carefully, the name of the primary key constraint is still not the one i wanted, i.e. pk_test1

    I have also check the data dictionary for test1

    Code:
    mysql> select CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, CONSTRAINT_TYPE
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='test1';
    +-------------------+-----------------+--------------+-----------------+
    | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | CONSTRAINT_TYPE |
    +-------------------+-----------------+--------------+-----------------+
    | certify           | PRIMARY         | certify      | PRIMARY KEY     |
    +-------------------+-----------------+--------------+-----------------+
    1 row in set (0.02 sec)
    from http://dev.mysql.com/doc/refman/5.5/en/create-table.html

    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

    create_definition:
    col_name column_definition
    | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
    [index_option] ...
    | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
    [index_option] ...
    | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
    [index_name] [index_type] (index_col_name,...)
    [index_option] ...
    | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
    [index_option] ...
    | [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name,...) reference_definition
    | CHECK (expr)
    also there' another problem:

    Code:
    mysql> show index from test1;
    +-------+------------+----------+--------------+-------------+-----------+------
    -------+----------+--------+------+------------+---------+----------------------
    -------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
    nality | Sub_part | Packed | Null | Index_type | Comment | Index_comment
           |
    +-------+------------+----------+--------------+-------------+-----------+------
    -------+----------+--------+------+------------+---------+----------------------
    -------+
    | test1 |          0 | PRIMARY  |            1 | test1_id    | A         |
         0 |     NULL | NULL   |      | BTREE      |         | primary key index for
     test1 |
    +-------+------------+----------+--------------+-------------+-----------+------
    -------+----------+--------+------+------------+---------+----------------------
    -------+
    1 row in set (0.00 sec)
    if you take closer look, you will realize that the index type is BTREE type and not hash as desired.

    would appreciate if someone could pin point my mistake.

    thanks a lot!
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    1.
    Trying to give the PRIMARY KEY a different name is in my opinion like asking for trouble and I wouldn't expect it to work exactly as you want. Since the PRIMARY KEY is unique for each table there is no reason to give it a special name.

    2.
    But regarding your attempt to create a HASH index MySQL is unfortunately silently ignoring it because InnoDB doesn't support it and especially not for the PRIMARY KEY since it is integral in the internal storage format of InnoDB tables.
    I don't like the aspect of silent ignores by MySQL but it does it quite a bit here and there so reading the manual carefully is a good thing because that on the other hand is very thorough.
    /Stefan
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Originally Posted by sr
    Trying to give the PRIMARY KEY a different name is in my opinion like asking for trouble and I wouldn't expect it to work exactly as you want. Since the PRIMARY KEY is unique for each table there is no reason to give it a special name.
    It's good coding style to do so - and every other DBMS allows you to give any constraint a meaningful name.


    if you observed carefully, the name of the primary key constraint is still not the one i wanted, i.e. pk_test1
    This the usual MySQL way of doing things: even if some syntax is accepted without errors, it doesn't mean it is actually implemented. This behaviour is somewhat documented.
    Originally Posted by The manual
    The name of a PRIMARY KEY is always PRIMARY
    Or to put it the other way: there is no way you can give the primary key constraint a name other than PRIMARY.

    Comments on this post

    • mysqlnoob1 agrees : your answer is correct
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo