CODEWITHSUNDEEP
mysqlsqlmariadb
DEgITx
DEgITx

Reputation: 980

Mysql/Maria unique keys representation

I want to know how keys stored in mysql/mariadb database. As i know there are some types stored in information_schema.columns: PRI, MUL and UNI that means Primary Key, Key, and Unique Key respectively.

But it is not true.

MariaDB [test]> CREATE TABLE test_table(leftkey int unsigned, rightkey int unsigned, unique key(leftkey, rightkey));

MariaDB [test]> DESC test_table;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| leftkey  | int(10) unsigned | YES  | MUL | NULL    |       |
| rightkey | int(10) unsigned | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+

So i have 2 questions:

1) How do i detect that key type is Unique? Describe command shows that type is MUL, not UNI (that means simple key).

2) Where are key pair (in example: leftkey and rightkey) stored?

Upvotes: 1

Views: 233

Answers (2)

Namphibian
Namphibian

Reputation: 12221

The information_schema.columns describes the columns not the keys which are constraints. Columns are used in constraints so you need to use some other information_schema tables to get information.

You can use the following query to get some more information about constraints:

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE TK
 ON TC.CONSTRAINT_SCHEMA = TK.CONSTRAINT_SCHEMA
 AND TC.TABLE_SCHEMA = TK.TABLE_SCHEMA
 AND TC.TABLE_NAME   = TK.TABLE_NAME
 AND TC.CONSTRAINT_NAME = TK.CONSTRAINT_NAME

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562270

Don't bother with DESCRIBE, because as you have found, it gives ambiguous information in some cases.

Just use SHOW CREATE TABLE. That will show exactly the SQL syntax that would recreate the table. Then you can know exactly which keys exist, and what type.

mysql> show create table test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `leftkey` int(10) unsigned DEFAULT NULL,
  `rightkey` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `leftkey` (`leftkey`,`rightkey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

You can also query INFORMATION_SCHEMA.STATISTICS to get more precise information.

mysql> select * from information_schema.statistics where table_name='test_table'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: test
   TABLE_NAME: test_table
   NON_UNIQUE: 0
 INDEX_SCHEMA: test
   INDEX_NAME: leftkey
 SEQ_IN_INDEX: 1
  COLUMN_NAME: leftkey
    COLLATION: A
  CARDINALITY: 0
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: YES
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT: 
*************************** 2. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: test
   TABLE_NAME: test_table
   NON_UNIQUE: 0
 INDEX_SCHEMA: test
   INDEX_NAME: leftkey
 SEQ_IN_INDEX: 2
  COLUMN_NAME: rightkey
    COLLATION: A
  CARDINALITY: 0
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: YES
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT:

Or the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table has slightly different information:

mysql> select * from information_schema.key_column_usage where table_name = 'test_table'\G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test
              CONSTRAINT_NAME: leftkey
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: test_table
                  COLUMN_NAME: leftkey
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 2. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test
              CONSTRAINT_NAME: leftkey
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: test_table
                  COLUMN_NAME: rightkey
             ORDINAL_POSITION: 2
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL

Upvotes: 4

Related Questions