What is the "name" and "character" in MySQL indexes?

Question

Primary key definitions can have an optional character value, while Uniques can have either a character or a name. What is it and what are their differences? And what is the best way to use them?

Answer 1

symbolis the name of the constraint. If you need to refuse later, disable or enable the restriction that you identify it in the system using this symbol.

index_nameis the name of the index created with the help CREATE INDEXthat should be used to enforce the constraint UNIQUE. (An index is a database object that is separate from the table in which it is indexed.) If you do not specify the name of an existing index, the system will generate an index for you, and if you later disable the restriction, the index will be automatically reset and must be recreated. when the restriction is back on. If you specify an existing index, it will not be reset and recreated with a restriction, which will save significant time and processing power. Some other platforms also allow you to specify an existing index to enforce the primary key.

Hope this helps.

Answer 2

. , . , . . .

Doc

http://dev.mysql.com/doc/refman/5.5/en/create-table.html :

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] ...

Constraint

, PRIMARY UNIQUE , / CONSTRAINT.

? , , , .. :

create table test1 (
    id int not null auto_increment,
    national_id varchar(50) not null,
    firstname varchar(50) not null,
    lastname varchar(50) not null,
    age int not null,
    constraint pk_test1_id primary key (id),
    constraint uq_test1_national_id unique key (national_id),
    index idx_test1_age (age)
);

/ pk_test1_id. / uq_test1_national_id / idx_test1_age.

** ? **

show index from test1;

| Table | Non_unique | Key_name | ...
| test1 | 0 | PRIMARY ...
| test1 | 0 | uq_test1_national_id ...
| test1 | 1 | idx_test1_age ...

, non_unique , . . . Key_name PRIMARY .

, ?

select column_name, column_key 
from information_schema.`COLUMNS` 
where table_name = 'test1'

| column_name | column_key | 
| id | PRI | 
| national_id | UNI | 
...
| age | MUL | 

, information_schema.columns , .

, , .

. / , show create table test1 show index from test1 ..

-- drop unique key using its symbol
alter table test1 drop index uq_test1_national_id;

-- drop the primary key; since primary key was on auto_increment and that
-- auto_increment should be a key, I am forcing an index on id before
-- removing primary key
create index idx_test1_id on test1 (id);
alter table test1 drop primary key;

-- drop index using its symbol/name
alter table test1 drop index idx_test1_age;

;

, :

constraint uq_test1_national_id unique key uq_my_own_name (national_id),

show index from test1 uq_my_own_name. alter table ... drop index uq_my_own_name. , index_name.