How to Use Composite Primary Keys in a Table with Existing Data

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 在已经有数据的表里面如何使用联合主键

| username: TiDBer_微风轻吟

[TiDB Usage Environment] Production Environment
[TiDB Version] v7.5.1
[Encountered Problem: Problem Phenomenon and Impact]
Due to the large amount of data, importing .sql files from MySQL into TiDB always results in errors. Therefore, I used .txt format to import the data into the TiDB database. The data does not have a primary key, so I set hp_id and zp_id as a composite primary key (which works in MySQL). However, it reported an error saying that identical data as a primary key is not supported, even though the composite primary key should not have duplicates.
[Attachment: Screenshot/Log/Monitoring]

| username: xfworld | Original post link

Then set a composite primary key, TiDB supports it too.

Try running it with a DDL SQL statement, it might be due to poor client compatibility.

Or try using a different client tool.

| username: TiDBer_JUi6UvZm | Original post link

What client is so terrible?

| username: TiDBer_微风轻吟 | Original post link


| username: Jack-li | Original post link

I have the same issue when using Navicat.

| username: 啦啦啦啦啦 | Original post link

In my case, using Navicat works, but it’s best to directly use the ALTER statement to add.

| username: TiDBer_微风轻吟 | Original post link

You can directly create it using a statement.

| username: TiDBer_微风轻吟 | Original post link

It might be due to different versions, but there is no problem with using the statement.

| username: TiDBer_vJGTQABF | Original post link

Direct statement creation

| username: xiaoqiao | Original post link

create and see

| username: zhaokede | Original post link

Directly use SQL statements to add, do not add through the interface.