Geek asks and answers

Difference in creating FOREIGN KEY

What is the difference in the following two operations CREATE TABLE? (The first uses KEY, but the second does not.)

CREATE TABLE `title` (
    `title` VARCHAR(255) NOT NULL,
    `order_number` VARCHAR(35) NOT NULL,
    KEY `order_number` (`order_number`),
    CONSTRAINT `order_number_fk` FOREIGN KEY (`order_number`)
        REFERENCES `order` (`order_number`) ON DELETE CASCADE
)

CREATE TABLE `title` (
    `title` VARCHAR(255) NOT NULL,
    `order_number` VARCHAR(35) NOT NULL,
    CONSTRAINT `order_number_fk` FOREIGN KEY (`order_number`)
        REFERENCES `order` (`order_number`) ON DELETE CASCADE
)

Both of them create valid tables. How do they differ and which do I want to use?

+5
source share
1 answer

They are (almost *) the same.

When creating a foreign key constraint, an index is automatically created in the corresponding columns of the link table if a suitable index does not already exist.

On the FOREIGN KEY Limitations man page :

InnoDB , . , . , . , , . index_name, , , .

.

(*) , .

, ( ).

SHOW INDEX :

1:

Table  Non_unique  Key_name          Seq_in_index   Column_name   ...
title  1           order_number      1              order_number  ...

2:

Table  Non_unique  Key_name          Seq_in_index   Column_name   ...
title  1           order_number_fk   1              order_number  ...

, - .

, , , :

, , .

, , , (order_number, title):

CREATE INDEX ix_order_number_title ON title (order_number, title);

SHOW INDEX :

1:

Table  Non_unique  Key_name               Seq_in_index   Column_name   ...
title  1           order_number           1              order_number  ...
title  1           ix_order_number_title  1              order_number  ...
title  1           ix_order_number_title  2              title         ...

2:

Table  Non_unique  Key_name               Seq_in_index   Column_name   ...
title  1           ix_order_number_title  1              order_number  ...
title  1           ix_order_number_title  2              title         ...

, , - . , , . , .

?

.

, :

  • , ,
  • , .
+7

More articles:


All Articles