Skip to Main Content

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MySQL DDL operation - add primary

seon hee parkApr 1 2024 — edited Apr 1 2024

MySQL Community Server 8.0.33

through MySQL guide(https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations) , it is possible to add primary key using the ALGORITHM=COPY clause, MySQL converts NULL values in the associated columns to default values.

But, when i do this operation, it is not working. I forgot something?

> show create table test_pk;

+---------+------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+---------+------------------------------------------------------------------------------------------------------------------------------------------------+

| test_pk | CREATE TABLE `test_pk` (

`p` bigint DEFAULT NULL,

`a` **char**(10) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

+---------+------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

> **select** * **from** test_pk;

+------+---+

| p | a |

+------+---+

| 1 | a |

| NULL | b |

| NULL | c |

| NULL | d |

| NULL | e |

+------+---+

> alter table test_pk add primary key (a,p), algorithm=copy;

ERROR 1265 (01000): Data truncated **for** column 'p' at row 2

> show variables like 'sql_mode';

+---------------+--------------------------------------------------------------+

| Variable_name | Value |

+---------------+--------------------------------------------------------------+

| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION |

+---------------+--------------------------------------------------------------+

1 row in set (0.01 sec)

THANKS.

Comments
Post Details
Added on Apr 1 2024
1 comment
203 views